Learn Oracle ebs R12

Query to retrieve KFF's in Oracle E-Business Suite

Query to retrieve KFF's in Oracle E-Business Suite

SELECT COUNT (*), fat.application_name

FROM fnd_id_flexs fif, fnd_application_tl fat

WHERE fat.application_id = fif.application_id

AND fat.LANGUAGE = 'US'

GROUP BY fat.application_name

ORDER BY fat.application_name

SELECT a.application_short_name, fat.application_name, fif.id_flex_code,

fif.id_flex_name

FROM fnd_id_flexs fif, fnd_application fa, fnd_application_tl fat

WHERE fa.application_id = fif.application_id

AND fat.application_id = fa.application_id

AND fat.LANGUAGE = 'US'ORDER BY fat.application_name, fif.id_flex_name

Delete Po using plsql Script po_headers_sv1.delete_po

PO Delete *******

DECLARE
lb_result BOOLEAN;
l_po_number VARCHAR2 (100) :='5952001681';-- &po_number;

CURSOR po_csr IS
SELECT poh.po_header_id
, poh.type_lookup_code
, poh.segment1 po_number
FROM po_headers_all poh
WHERE NVL (poh.closed_code, 'OPEN') = 'OPEN'
AND poh.segment1 = l_po_number;
BEGIN
FOR po_rec IN po_csr LOOP
lb_result := po_headers_sv1.delete_po
( x_po_header_id => po_rec.po_header_id
, x_type_lookup_code => po_rec.type_lookup_code
, p_skip_validation => 'Y' );

IF lb_result = TRUE THEN
-- DBMS_OUTPUT.PUT_LINE('PO Number Deleted '|| l_po_number);
DBMS_OUTPUT.put_line ( 'Successfully Deleted the PO');
COMMIT;
ELSE
-- DBMS_OUTPUT.PUT_LINE ('API Failed to delete the Purchase Order ' || l_po_number);
DBMS_OUTPUT.put_line ( 'Failed to Delete the PO');
ROLLBACK;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Unexpected Exception while deleting PO~' || l_po_number || '~' || SQLERRM);
ROLLBACK;
END;

Cancel po line using Plsql script PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT

************** Cancel PO Line *****************#

DECLARE
v_return_status VARCHAR2 (10);
v_msg_data VARCHAR2(1000);
v_po_header_id NUMBER := 2503180;
v_doc_subtype VARCHAR2(10) := 'STANDARD';
v_doc_type VARCHAR2(10) := 'PO';
v_org_id NUMBER := 894;
v_action VARCHAR2(10) := 'CANCEL';
v_action_date DATE := SYSDATE;
l_user_id NUMBER :=40231;
l_resp_id NUMBER :=20707;
l_appl_id NUMBER :=201;

BEGIN

fnd_global.apps_initialize(l_user_id, l_resp_id, l_appl_id);
MO_GLOBAL.INIT('PO');
mo_global.set_policy_context ('S',v_org_id);
DBMS_OUTPUT.PUT_LINE ('Calling API For Cancelling Documents');
PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT (p_api_version => 1.0
,p_init_msg_list =>fnd_api.g_true
,p_commit =>fnd_api.g_false
,x_return_status =>v_return_status
,p_doc_type =>v_doc_type
,p_doc_subtype =>v_doc_subtype
,p_doc_id =>v_po_header_id
,p_doc_num =>NULL
,p_release_id =>NULL
,p_release_num =>NULL
,p_doc_line_id =>NULL
,p_doc_line_num =>NULL
,p_doc_line_loc_id =>NULL
,p_doc_shipment_num => NULL
,p_action => v_action
,p_action_date => v_action_date
,p_cancel_reason => 'OLD PURCHASE ORDER'
,p_cancel_reqs_flag => 'N'
,p_print_flag => NULL
,p_note_to_vendor => NULL
,p_use_gldate => NULL
,p_org_id => v_org_id);
COMMIT;
DBMS_OUTPUT.PUT_LINE('The Return Status of the API : '|| v_return_status);
IF v_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE ('Cancellation of PO is Sucessfull : '||v_po_header_id) ;
ELSE
DBMS_OUTPUT.PUT_LINE ('Cancellation of PO Failed ');
ROLLBACK;
FOR i IN 1 .. FND_MSG_PUB.COUNT_MSG LOOP
v_msg_data := FND_MSG_PUB.GET( p_msg_index =>i,p_encoded => 'F');
DBMS_OUTPUT.PUT_LINE( i|| ') '|| v_msg_data);
END LOOP;
END IF;
END;

PO Approval Hierarchy Query -R12

SELECT DISTINCT

pah.object_id, pha.segment1 AS PO_NUMBER, pah.Action_Code,
pah.Action_Date, papf.full_name AS performed_by, pah.Note,
pha.amount_limit, pha.currency_code,
pha.rate, pha.blanket_total_amount,
(pha.rate * pha.blanket_total_amount) AS BLANKET_TOTAL_AMOUNT_CAD,
abc.second_sign, pah.object_revision_num AS Revision_Number
FROM po_action_history pah,
per_all_people_f papf,
po_headers_all pha,
( SELECT object_id,
           Action_Code,
           object_revision_num,
           CASE WHEN COUNT (Action_Code) <= 1 THEN 'N' ELSE 'Y' END
           AS SECOND_SIGN
       FROM po_action_history
       WHERE 1 = 1 AND Action_Code = 'FORWARD'
           AND object_sub_type_code = 'BLANKET'
       GROUP BY object_id, Action_Code, object_revision_num
       HAVING COUNT (Action_Code) > 0) abc
WHERE pah.action_code = 'APPROVE'
   AND pah.employee_id = papf.person_id
   AND pah.object_id = pha.po_header_id
   AND pah.object_id = abc.object_id
   AND pah.object_revision_num = abc.object_revision_num
   AND pha.segment1 = 'XX_PO_NUMBER' -- PO Number
   AND pah.object_sub_type_code = 'BLANKET'
ORDER BY pha.segment1 ASC, pah.object_revision_num

Query to find AP Supplier Remittance Email Addresses

The following query will display all the active vendors' email addresses by their associated sites. This will work only in Oracle R12.

SELECT

-- s.vendor_id,

-- st.vendor_site_id,

-- s.party_id,

-- st.party_site_id,

s.vendor_name "Vendor Name",

s.segment1 "Vendor Number",

s.vendor_type_lookup_code "Vendor Type",

st.vendor_site_code "Vendor Site Code",

ou.name "Operating Unit",

iepa.remit_advice_delivery_method "Remittance Delivery Method",

iepa.remit_advice_email "Remittance Advice Email"

FROM

ap.ap_suppliers s,

ap.ap_supplier_sites_all st,

hr_operating_units ou,

iby.iby_external_payees_all iepa

WHERE

1=1

-- AND s.vendor_type_lookup_code = 'EMPLOYEE'

AND TRUNC (SYSDATE) BETWEEN TRUNC (s.start_date_active) AND TRUNC (NVL (s.end_date_active, SYSDATE+1))

AND s.enabled_flag = 'Y'

AND iepa.supplier_site_id = st.vendor_site_id

AND iepa.payee_party_id = s.party_id

AND st.org_id = ou.organization_id

AND st.vendor_id = s.vendor_id

ORDER BY s.vendor_name, st.vendor_site_code;

How to open Periods in Oracle Apps

To Create sales order, for a given month(period), that month should be opened in five Modules.

· GL-> Setup-> Open /Close.

· AP-> Accounting ->Control Payable Periods.

· PO->Setup->Financials-> Accounting->Open/Close Periods.

· INV->Accounting Close Cycle ->Inventory Accounting Period->(Select Org )

· AR->Control ->Accounting ->Open/Close Periods.

How to set the org context in Oracle ebs 11i and R12

Set org context in R12

1. The SQL command to set the ORG_ID prior to running a script is:

SQL> exec mo_global.init('PN');

exec mo_global.set_policy_context('S','org_id');

The procedure mo_global.set_policy_context has two parameters

p_access_mode and p_org_id

p_access_mode

S ( In case you want your current session to work against single org_id)

M (In case you want your current session to work against multiple org_id )

p_org_id: only applicable if p_access_mode is passed value of "S"

Search This Blog

Query to retrieve KFF's in Oracle E-Business Suite

Delete Po using plsql Script po_headers_sv1.delete_po

SELECT DISTINCT

Query to find AP Supplier Remittance Email Addresses

Set org context in R12