select dbms_metadata.get_ddl('VIEW','
Tuesday 28 November 2023
Oracle EBS SQL Query - To Get the Object Source Code
Friday 17 November 2023
Oracle EBS - SQL Query to get Inventory Interface Manager Status
SELECT
x.process_type "Name",
decode((
SELECT
'1'
FROM
apps.fnd_concurrent_requests cr, apps.fnd_concurrent_programs_vl cp, apps.fnd_application a
WHERE
cp.concurrent_program_id = cr.concurrent_program_id
AND cp.concurrent_program_name = x.process_name
AND cp.application_id = a.application_id
AND a.application_short_name = x.process_app_short_name
AND phase_code != 'C'
AND ROWNUM = 1
),
'1',
'Active',
'Inactive') "Status",
x.worker_rows "Worker Rows",
x.timeout_hours "Timeout Hours",
x.timeout_minutes "Timeout Minutes",
x.process_hours "Process Interval Hours",
x.process_minutes "Process Interval Minutes",
x.process_seconds "Process Interval Seconds"
FROM
(
SELECT
mipc.process_code,
mipc.process_status,
mipc.process_interval,
mipc.manager_priority,
mipc.worker_priority,
mipc.worker_rows,
mipc.processing_timeout,
mipc.process_name,
mipc.process_app_short_name,
a.meaning process_type,
floor(mipc.process_interval / 3600) process_hours,
floor((mipc.process_interval -(floor(mipc.process_interval / 3600) * 3600)) / 60) process_minutes,
( mipc.process_interval - ( floor(mipc.process_interval / 3600) * 3600 ) - ( floor((mipc.process_interval -(floor(mipc.
process_interval / 3600) * 3600)) / 60) * 60 ) ) process_seconds,
floor(mipc.processing_timeout / 3600) timeout_hours,
floor((mipc.processing_timeout - floor(mipc.processing_timeout / 3600) * 3600) / 60) timeout_minutes
FROM
apps.mtl_interface_proc_controls mipc,
apps.mfg_lookups a
WHERE
a.lookup_type = 'PROCESS_TYPE'
AND a.lookup_code = mipc.process_code
) x
-- WHERE x.PROCESS_TYPE = 'Cost Manager' -- uncomment this to display only the cost manager; Possible Values: Cost Manager; Lot Move Transaction; Material transaction; Move transaction
ORDER BY
1;
Saturday 11 November 2023
Oracle EBS - SQL Query to check Purchase Order Receipt Routing Type
TABLE: RCV_TRANSACTIONS
COLUMN: ROUTING_HEADER_ID
VALUES:
1 (Standard Receipt),
2 (Inspection Required) And
3 (Direct Delivery).
SQL Query:
SELECT pha.segment1 po_number,
pla.line_num po_line_number,
pra.release_num po_release_number,
rsh.receipt_num receipt_number,
decode(rt.routing_header_id,1,'Standard Receipt', 2, 'Inspection Required', 3, 'Direct Delivery',null) Receipt_Routing,
msik.concatenated_segments item_number,
msik.description,
rs.unit_of_measure,
rs.item_revision,
rs.receipt_date,
plla.quantity total_quantity,
rs.quantity quantity_to_inspect,
plla.quantity_received,
plla.quantity_accepted,
plla.quantity_rejected,
rt.vendor_lot_num,
rt.comments vendor_serial_num,
rsh.bill_of_lading customs_number,
rsh.comments customs_date,
rt.attribute1 manufactured_date,
rs.po_line_id,
rs.po_header_id,
rs.po_release_id,
rs.po_line_location_id,
rs.shipment_header_id,
rs.shipment_line_id,
rs.rcv_transaction_id,
rs.item_id,
rs.to_organization_id,
rs.supply_source_id,
mp.organization_code
FROM apps.rcv_supply rs,
apps.mtl_system_items_kfv msik,
apps.po_headers_all pha,
apps.po_lines_all pla,
apps.po_releases_all pra,
apps.po_line_locations_all plla,
apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.mtl_parameters mp
WHERE rs.to_organization_id = mp.organization_id
AND mp.organization_code IN ('SKO', 'TIF', 'TIS', 'TIW')
AND rs.supply_type_code = 'RECEIVING'
AND rs.po_header_id = pha.po_header_id
AND msik.inventory_item_id = rs.item_id
AND msik.organization_id = rs.to_organization_id
AND rs.po_line_id = pla.po_line_id
AND rs.po_release_id = pra.po_release_id(+)
--AND TRUNC(rs.receipt_date) = TRUNC(SYSDATE)
AND rs.po_line_location_id = plla.line_location_id
AND rs.rcv_transaction_id = rt.transaction_id
AND rt.transaction_type = 'RECEIVE'
AND rt.inspection_status_code = 'NOT INSPECTED'
AND rs.shipment_header_id = rsh.shipment_header_id
-- AND pha.segment1='333428'
-- AND rt.routing_header_id=2 -- 1 (Standard receipt), 2 (Inspection required) and 3 (Direct delivery)
ORDER BY 9,
5,
1,
2,
4;
Monday 9 October 2023
Oracle EBS - How to Enable New Tax Setup
Step1: Need to assign below responsibility to in User Account for Tax Setup Configuration
Responsibility:
Tax Managers
Step2: New Tax Setup for ‘FREMONT
CA-PARTIAL’
Tax Regime
Name: STATE TAX
Tax Regime: STATE TAX
Tax Name: FREMONT CA - PARTIAL
Tax
Jurisdiction: FREMONT
CA - PARTIAL
Tax Status: FREMONT CA - PARTIAL
Tax Rate
Code: FREMONT CA - PARTIAL
Tax
Classification: FREMONT CA - PARTIAL
Configuration
Owner: Global
Configuration Owner
Rate Type: Percentage
%Rate: 6.3125
Step1: Define Tax Regime:
(Note: No Need to perform Regime Setup If Regime Setup
already Exists)
Navigation: Tax Manager Responsibility
> Tax Configuration > Tax Regime
Click
on Create Button.
Tax Regime: STATE TAX
Click on Apply.
Step2: Define Tax
Navigation: Tax Manager Responsibility > Tax Configuration >
Taxes
Click on Create Button.
Tax Name: FREMONT CA - PARTIAL
Click on ‘Tax Account’ button
Step3: Define Tax Jurisdictions
Navigation: Tax Manager Responsibility > Tax Configuration > Tax
Juridictions
Click
on Create Button
Step4: Define
Tax Statuses
Navigation: Tax Manager Responsibility > Tax Configuration > Tax
Statuses
Click on Create Button.
Step5: Define Tax Rates.
Navigation: Tax Manager Responsibility
> Tax Configuration > Tax Rates
Click
on Create Button
Configuration
Owner: Global Configuration Owner
Tax Rate Code: FREMONT CA - PARTIAL
Rate Type:
Percentage
%Rate: 6.3125
Click on Rate
Details Icon.
Tax Rate Detail:
Tax Rate
Name: FREMONT CA - PARTIAL
Tax Rate
Description: FREMONT CA - PARTIAL
Click on Apply.
Tax Account:
Click on Tax
Account Icon.
Step6: Define Tax Rule
Navigation: Tax Manager Responsibility
> Tax Configuration > Tax Rules
Need to Search with
Configuration Owner: Global
Configuration Owner
Tax Regime: STATE TAX
Tax Name: FREMONT CA - PARTIAL
Need to update the following details.
Determine Tax
Status: FREMONT CA - PARTIAL
Determine Tax
Rate: FREMONT CA - PARTIAL
Determine
Taxable Basis: STANDARD_TB
Calculate Tax
Amounts: STANDARD_TC
Tax Rule Code: P2P_TAX_CODE_NOT_APPLY_OM
Name: P2P_TAX_CODE_NOT_APPLY_OM
Click on Next
Location Type:
Bill To
Geography Type:
Country
Operator: Not
equal to
Value: Afghanistan
Click on Next
Click on Next
Determining
Factor Set: FCP_DFS_P2P_1
Name: FCP_DFS_P2P_1
Condition
Set: FCP_CS_P2P_1
Name: FCP_CS_P2P_1
Click on Finish.
Click on Pencil
Icon.
Select Enable
Checkbox
Click on Next
& Finish.
Step7: Now
we Need to Enable Tax.
Navigation: Tax Manager Responsibility > Tax Configuration > Taxes
Country: United
States
Regime: STATE TAX
Tax Rate Name: FREMONT CA - PARTIAL
Search with above values
Click on Update
Button.
Enable the Checkbox and click on Apply
Now we can test
the Tax Setup on Purchase Order Transaction.
Tuesday 12 September 2023
Oracle EBS - PLSQL Script to check Chart of Account is restricted by which Cross Validation Rule
Subject: PLSQL Script to check Chart of Account Is Restricted by which Cross Validation Rule.
---PLSQL Code---
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
p_conc_segments VARCHAR2(200) := '47.2020.65100.00.000.0000' ;
--'&Conc_Segments';
p_accounting_flex VARCHAR2(100) := 'Accounting Flexfield';
--'&Flex_Structure';
CURSOR c_flex_rules(cp_id_flex_num NUMBER)
IS
SELECT
R.flex_validation_rule_name rule_name,
T.error_message_text error_message,
nvl(to_number(to_char(R.start_date_active,'J')), 0) start_date,
nvl(to_number(to_char(R.end_date_active,'J')), 0) end_date,
L.include_exclude_indicator IE_indicator,
L.concatenated_segments_low segments_low,
L.concatenated_segments_high segments_high
FROM
fnd_flex_validation_rule_lines L,
fnd_flex_validation_rules R,
FND_FLEX_VDATION_RULES_TL T
WHERE
R.enabled_flag = 'Y' AND
L.enabled_flag = R.enabled_flag AND
R.application_id = L.application_id AND
R.application_id = T.application_id AND
R.flex_validation_rule_name = L.flex_validation_rule_name AND
R.flex_validation_rule_name = T.flex_validation_rule_name AND
R.application_id = 101 AND
R.id_flex_num = L.id_flex_num AND
R.id_flex_num = T.id_flex_num AND
R.id_flex_num = cp_id_flex_num AND
R.id_flex_code = L.id_flex_code AND
R.id_flex_code = T.id_flex_code AND
T.language = 'US' AND
R.id_flex_code = 'GL#' AND
trunc(nvl(R.start_date_active, sysdate)) <= trunc(sysdate) AND
trunc(nvl(R.end_date_active, sysdate+1)) > trunc(sysdate)
ORDER BY
R.flex_validation_rule_name asc, L.Include_Exclude_Indicator desc;
v_flex_rule c_flex_rules%ROWTYPE;
v_delimiter VARCHAR2(1);
v_id_flex_num NUMBER;
v_invalid BOOLEAN;
v_seg_count NUMBER;
v_segment VARCHAR2(100);
v_segments FND_FLEX_EXT.SegmentArray;
v_segments_low FND_FLEX_EXT.SegmentArray;
v_segments_high FND_FLEX_EXT.SegmentArray;
v_temp NUMBER;
i NUMBER;
v_rule_name
fnd_flex_vdation_rules_vl.flex_validation_rule_name%TYPE;
v_intro VARCHAR2(100);
v_result VARCHAR2(2000);
CRLF VARCHAR2(1) := CHR(10);
BEGIN
v_intro := CRLF||'For flex structure
"'||p_accounting_flex||'" the combination '||CRLF||'"'||p_conc_segments||'"';
v_result := ' ';
BEGIN
SELECT concatenated_segment_delimiter, id_flex_num
INTO v_delimiter, v_id_flex_num
FROM fnd_id_flex_structures FS
WHERE
application_id = 101 AND
id_flex_code = 'GL#' AND
id_flex_num =
(SELECT min(id_flex_num)
FROM fnd_id_flex_structures_tl
WHERE
application_id = 101 AND
id_flex_code = 'GL#' AND
id_flex_structure_name = p_accounting_flex);
EXCEPTION
WHEN NO_DATA_FOUND THEN BEGIN
raise_application_error(-20001, 'Unable to locate the flex structure
data.');
END;
END;
v_seg_count :=
FND_FLEX_EXT.Breakup_Segments (p_conc_segments, v_delimiter, v_segments);
OPEN c_flex_rules(v_id_flex_num);
FETCH c_flex_rules INTO v_flex_rule;
<<rule_cursor>>
LOOP
EXIT WHEN c_flex_rules%NOTFOUND;
v_temp := FND_FLEX_EXT.Breakup_Segments (v_flex_rule.segments_low,
v_delimiter, v_segments_low);
v_temp := FND_FLEX_EXT.Breakup_Segments (v_flex_rule.segments_high,
v_delimiter, v_segments_high);
i
:= 1;
IF
v_flex_rule.IE_indicator = 'I' THEN
v_rule_name := v_flex_rule.rule_name;
v_invalid := FALSE;
<<include>>
WHILE v_flex_rule.rule_name = v_rule_name AND -- cycle on Include lines
of a rule
v_flex_rule.IE_indicator = 'I' AND
NOT v_invalid AND -- until one Include line is found
c_flex_rules%FOUND LOOP -- including the cc in question
v_invalid := FALSE;
FOR i IN 1 .. v_seg_count LOOP
v_segment := v_segments(i);
IF v_segments(i) NOT BETWEEN v_segments_low(i) AND v_segments_high(i)
THEN
v_invalid := TRUE;
END IF;
END LOOP;
FETCH c_flex_rules INTO v_flex_rule;
v_temp := FND_FLEX_EXT.Breakup_Segments (v_flex_rule.segments_low,
v_delimiter, v_segments_low);
v_temp := FND_FLEX_EXT.Breakup_Segments (v_flex_rule.segments_high,
v_delimiter, v_segments_high);
END LOOP include;
IF v_invalid THEN
v_result := v_result ||CRLF||'Not included in
"'||v_rule_name||'" rule.';
END IF;
ELSE
<<exclude>>
LOOP
v_segment := v_segments(i);
EXIT exclude WHEN v_segments(i) NOT BETWEEN v_segments_low(i) AND
v_segments_high(i);
IF (i = v_seg_count) THEN
v_result := v_result ||CRLF||'Excluded by
"'||v_flex_rule.rule_name||'" rule.';
v_invalid := TRUE;
EXIT exclude;
END IF;
i := i + 1;
END LOOP exclude;
FETCH c_flex_rules INTO v_flex_rule;
END IF;
END LOOP rule_cursor;
IF v_result <> ' ' THEN
dbms_output.put_line( v_intro ||' is ' ||v_result );
ELSE
dbms_output.put_line( v_intro||' does not violate any cross-validation
rules.');
END IF;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20002, SQLERRM);
END;
/
Output:
Subscribe to:
Posts (Atom)