Oracle EBS (ERP): PO

Showing posts with label PO. Show all posts

PO output for communication customization in r12

Find Document Type Template

Document Name: Standard Purchase Order

Document Type Layout : XXJG Standard Purchase Order XML

Contract Terms Layout : Oracle Contract Terms Template

XML Publisher -> Home -> Template

Data Definition: Standard Purchase Order Data Source

Application: Purchasing

Main View:

PO_HEADERS_XML

PO_LINES_XML

PO_LINE_LOCATIONS_XML

PO_DISTRIBUTION_XML

PO_HEADERS_ARCHIVE_XML

PO_LINES_ARCHIVE_XML

PO_LINE_LOCATIONS_ARCHIVE_XML

SELECT phx.*, PO_COMMUNICATION_PVT.getIsComplexWorkPO() is_complex_work_po, PO_COMMUNICATION_PVT.getDocumentType() document_type, PO_COMMUNICATION_PVT.getCoverMessage() cover_message,

PO_COMMUNICATION_PVT.getTimezone() timezone,PO_COMMUNICATION_PVT.getAmendmentMessage() ammendment_message,PO_COMMUNICATION_PVT.getTestFlag() test_flag,

PO_COMMUNICATION_PVT.getDistinctShipmentCount() DIST_SHIPMENT_COUNT, PO_COMMUNICATION_PVT.getFirstNonCancelledPll() NON_CANCELLED_PLL ,

PO_COMMUNICATION_PVT.getDocumentName() document_name,

PO_COMMUNICATION_PVT.IsDocumentSigned(PO_COMMUNICATION_PVT.getDocumentId()) Signed,

fnd_profile.value('PO_GENERATE_AMENDMENT_DOCS') amendment_profile,PO_COMMUNICATION_PVT.getWithTerms() With_Terms , PO_COMMUNICATION_PVT.getIsContractAttachedDoc() Is_Attached_Doc , CURSOR (SELECT message_name message, message_text text FROM fnd_new_messages WHERE message_name in (

'PO_WF_NOTIF_REVISION',

'PO_WF_NOTIF_VENDOR_NO',

'PO_WF_NOTIF_PAYMENT_TERMS',

'PO_WF_NOTIF_FREIGHT_TERMS',

'PO_WF_NOTIF_FOB',

'PO_WF_NOTIF_SHIP_VIA',

'PO_WF_NOTIF_CONFIRM_TO_TELE',

'PO_WF_NOTIF_REQUESTER_DELIVER',

'PO_WF_NOTIF_DESCRIPTION',

'PO_WF_NOTIF_TAX',

'PO_WF_NOTIF_UOM',

'PO_WF_NOTIF_UNIT_PRICE',

'PO_WF_NOTIF_QUANTITY',

'PO_WF_NOTIF_PURCHASE_ORDER',

'PO_WF_NOTIF_BUYER',

'PO_WF_NOTIF_AMOUNT',

'PO_WF_NOTIF_EFFECTIVE_DATE',

'PO_WF_NOTIF_HEADER_NOTE',

'PO_WF_NOTIF_LINE_NUMBER',

'PO_WF_NOTIF_LINE_PAYITEM_NUM', -- <Complex Work R12>

'PO_WF_NOTIF_MULTIPLE',

'PO_WF_NOTIF_PART_NO_DESC',

'PO_WF_NOTIF_SUPPLIER_ITEM',

'PO_WF_NOTIF_TOTAL',

'PO_WF_NOTIF_NOTE',

'PO_FO_PACKING_INSTRUCTION',

'PO_FO_CUST_PO_NUMBER',

'PO_FO_CUST_ITEM_DESC',

'PO_FO_LINE_NUMBER',

'PO_FO_SHIP_NUMBER',

'PO_FO_AMOUNT_BASED',

'PO_FO_CONTRACTOR_NAME',

'PO_FO_START_DATE',

'PO_FO_END_DATE',

'PO_FO_WORK_SCHEDULE',

'PO_FO_SHIFT_PATTERN',

'PO_FO_PRICE_DIFFERENTIALS',

'PO_FO_DELIVER_TO_LOCATION',

'PO_FO_EFFECTIVE_START_DATE',

'PO_FO_AMOUNT_AGREED',

'PO_FO_ADVANCE', -- <Complex Work R12>

'PO_FO_RETAINAGE_RATE', -- <Complex Work R12>

'PO_FO_MAX_RETAINAGE_AMOUNT', -- <Complex Work R12>

'PO_FO_PROGRESS_PAYMENT_RATE', -- <Complex Work R12>

'PO_FO_RECOUPMENT_RATE', -- <Complex Work R12>

'PO_FO_PRICE_BREAK',

'PO_FO_CHARGE_ACCOUNT',

'PO_FO_CONTRACTOR',

'PO_FO_CONTACT_NAME',

'PO_FO_TELEPHONE',

'PO_FO_FAX',

'PO_FO_NAME',

'PO_FO_TITLE',

'PO_FO_DATE',

'PO_FO_REVISION',

'PO_FO_AMENDMENT',

'PO_FO_SHIP_METHOD',

'PO_FO_SHIPPING_INSTRUCTION',

'PO_FO_DRAFT',

'PO_FO_PROPRIETARY_INFORMATION',

'PO_FO_TRANSPORTAION_ARRANGED',

'PO_FO_DELIVER_TO_LOCATION',

'PO_FO_NO',

'PO_FO_COMPANY',

'PO_FO_SUBMIT_RESPONSE',

'PO_FO_EMAIL',

'PO_WF_NOTIF_EXPIRES_ON',

'PO_FO_TEST',

'PO_FO_ORG_AGR_ASS',

'PO_FO_EFFECTIVE_END_DATE',

'PO_FO_PURCHASING_ORGANIZATION',

'PO_FO_PURCHASING_SUPPLIER_SITE',

'PO_FO_TRANSPORTATION_ARRANGED',

'PO_WF_NOTIF_ADDRESS',

'PO_WF_NOTIF_ORDER',

'PO_WF_NOTIF_ORDER_DATE',

'PO_FO_VENDOR',

'PO_FO_SHIP_TO',

'PO_FO_BILL_TO',

'PO_FO_CONFIRM_NOT_DUPLICATE',

'PO_FO_AGREEMENT_CANCELED',

'PO_FO_FORMAL_ACCEPT',

'PO_FO_TYPE',

'PO_FO_REVISION_DATE',

'PO_FO_REVISED_BY',

'PO_FO_PRICES_EXPRESSED',

'PO_FO_NOTES',

'PO_WF_NOTIF_PREPARER',

'PO_FO_SUPPLIER_CONFIGURATION',

'PO_FO_DELIVER_DATE_TIME',

'PO_FO_LINE_REF_BPA',

'PO_FO_LINE_REF_CONTRACT',

'PO_FO_LINE_SUPPLIER_QUOTATION',

'PO_FO_USE_SHIP_ADDRESS_TOP',

'PO_FO_LINE_CANCELED',

'PO_FO_ORIGINAL_QTY_ORDERED',

'PO_FO_QUANTITY_CANCELED',

'PO_FO_SHIPMENT_CANCELED',

'PO_FO_ORIGINAL_SHIPMENT_QTY',

'PO_FO_CUSTOMER_ACCOUNT_NO',

'PO_FO_RELEASE_CANCELED',

'PO_FO_PO_CANCELED',

'PO_FO_TOTAL',

'PO_FO_SUPPLIER_ITEM',

'PO_FO_ORIGINAL_AMOUNT_ORDERED',

'PO_FO_AMOUNT_CANCELED',

'PO_FO_UN_NUMBER',

'PO_WF_NOTIF_PROMISED_DATE',

'PO_WF_NOTIF_NEEDBY_DATE',

'PO_FO_HAZARD_CLASS',

'PO_FO_PAGE',

'PO_FO_REFERENCE_DOCUMENTS',

'PO_FO_PAYITEM_CANCELED', --<Bug#: 4899200>

'PO_FO_ORIGINAL_PAYITEM_QTY', --<Bug#: 4899200>

'PO_FO_PAYITEM_QTY_CANCELED', --<Bug#: 4899200>

'PO_FO_ORIGINAL_PAYITEM_AMT', --<Bug#: 5464968>

'PO_FO_MODIFIER_TYPE', --Enhanced Pricing

'PO_FO_BLANKET', --Enhanced Pricing

'PO_FO_BLANKET_PRICE_STRUCT', --Enhanced Pricing

'PO_FO_MODIFIER_DESC', --Enhanced Pricing

'PO_FO_RATE', --Enhanced Pricing

'PO_FO_ADJUSTED_AMT', --Enhanced Pricing

'PO_FO_RATE_APP_METHOD', --Enhanced Pricing

'PO_FO_LIST_LINE_PRICE', --Enhanced Pricing

'PO_FO_PAYITEM_AMT_CANCELED', --<Bug#: 5464968>

'PO_FO_USE_SHIP_ADDRESS' --Bug 9855114

) AND application_id = 201 AND language_code = 'US') AS message,CURSOR( SELECT fds.short_text

FROM

fnd_attached_docs_form_vl fad,

fnd_documents_short_text fds

WHERE ((entity_name = 'PO_HEADERS' AND

pk1_value = to_char(phx.po_header_id)) OR

(entity_name = 'PO_VENDORS' AND

pk1_value = to_char(phx.vendor_id)) OR

(entity_name = 'PO_VENDOR_SITES' AND

pk1_value = to_char(phx.vendor_site_id))) AND -- bug6154354

function_name = 'PO_PRINTPO'

AND fad.media_id = fds.media_id

AND fad.datatype_id=1 order by fad.seq_num) AS header_short_text,CURSOR(

SELECT fad.url web_page --Bug#4958642

FROM fnd_attached_docs_form_vl fad

WHERE ((fad.entity_name='PO_HEADERS' AND fad.pk1_value=to_char(phx.po_header_id)) OR

(fad.entity_name='PO_VENDORS' AND fad.pk1_value=to_char(phx.vendor_id)) OR

(fad.entity_name='PO_VENDOR_SITES' AND fad.pk1_value=to_char(phx.vendor_site_id)) --bug6154354

)

AND fad.datatype_id=5

AND fad.function_name='PO_PRINTPO'

order by fad.entity_name,fad.seq_num) AS header_url_attachments,CURSOR(

SELECT fad.file_name

FROM fnd_attached_docs_form_vl fad

WHERE ((fad.entity_name='PO_HEADERS' AND fad.pk1_value=to_char(phx.po_header_id)) OR

(fad.entity_name='PO_VENDORS' AND fad.pk1_value=to_char(phx.vendor_id)) OR

(fad.entity_name='PO_VENDOR_SITES' AND fad.pk1_value=to_char(phx.vendor_site_id)) --bug6154354

)

AND fad.datatype_id=6

AND fad.function_name='PO_PRINTPO'

order by fad.entity_name,fad.seq_num) AS header_file_attachments,

CURSOR(SELECT plx.*, CURSOR(SELECT PRICE_TYPE_DSP PRICE_TYPE, MULTIPLIER FROM po_price_differentials_v

WHERE entity_type='PO LINE' AND entity_id = plx.po_line_id and enabled_flag='Y') AS price_diff,

CURSOR( SELECT plx.po_line_id , fds.short_text

FROM

fnd_attached_docs_form_vl fad,

fnd_documents_short_text fds

WHERE ((fad.entity_name='PO_LINES' AND fad.pk1_value=to_char(plx.po_line_id))

(fad.entity_name='MTL_SYSTEM_ITEMS' AND

fad.pk1_value=to_char(PO_COMMUNICATION_PVT.getInventoryOrgId()) AND --Bug6139548

fad.pk2_value=to_char(plx.item_id) AND plx.item_id is not null)

(fad.entity_name='PO_HEADERS' AND fad.pk1_value=to_char(plx.from_header_id)

AND plx.from_header_id IS NOT NULL)

(fad.entity_name='PO_IN_GA_LINES' AND fad.pk1_value=to_char(plx.from_line_id)

AND plx.from_line_id IS NOT NULL)

(fad.entity_name='PO_HEADERS' AND fad.pk1_value=to_char(plx.CONTRACT_ID)

AND plx.CONTRACT_ID IS NOT NULL)

) AND

function_name = 'PO_PRINTPO'

AND fad.media_id = fds.media_id

AND fad.datatype_id=1 order by fad.seq_num) AS line_short_text,CURSOR (SELECT pax1.* FROM PO_PRICE_ADJUSTMENTS_XML pax1 WHERE pax1.po_header_id = plx.po_header_id AND pax1.po_line_id = plx.po_line_id AND pax1.parent_adjustment_id is null order by pax1.pricing_group_sequence) AS PRICE_MODIFIERS,CURSOR (SELECT pax2.* FROM PO_PRICE_ADJUSTMENTS_XML pax2 WHERE pax2.po_header_id = plx.from_header_id AND pax2.po_line_id = plx.from_line_id AND pax2.parent_adjustment_id is null order by pax2.pricing_group_sequence) AS ADD_PRICE_MODIFIERS,CURSOR (SELECT pha.segment1 ponum FROM po_headers_all pha WHERE pha.po_header_id = plx.from_header_id) AS ADD_PRICE_PONUM,CURSOR(

SELECT fad.url web_page --Bug#4958642

FROM fnd_attached_docs_form_vl fad

WHERE ((fad.entity_name='PO_LINES' AND fad.pk1_value=to_char(plx.po_line_id))

(fad.entity_name='PO_HEADERS' AND fad.pk1_value=to_char(plx.from_header_id)

AND plx.from_header_id IS NOT NULL)

(fad.entity_name='PO_IN_GA_LINES' AND fad.pk1_value=to_char(plx.from_line_id)

AND plx.from_line_id IS NOT NULL)

(fad.entity_name='PO_HEADERS' AND fad.pk1_value=to_char(plx.contract_id)

AND plx.contract_id IS NOT NULL)

(fad.entity_name='MTL_SYSTEM_ITEMS' AND

fad.pk1_value=to_char(PO_COMMUNICATION_PVT.getInventoryOrgId()) AND

fad.pk2_value=to_char(plx.item_id) AND plx.item_id is not null)

)

AND fad.datatype_id=5

AND fad.function_name='PO_PRINTPO' order by fad.seq_num) AS line_url_attachments,CURSOR(

SELECT fad.file_name

FROM fnd_attached_docs_form_vl fad

WHERE ((fad.entity_name='PO_LINES' AND fad.pk1_value=to_char(plx.po_line_id))

(fad.entity_name='PO_HEADERS' AND fad.pk1_value=to_char(plx.from_header_id)

AND plx.from_header_id IS NOT NULL)

(fad.entity_name='PO_IN_GA_LINES' AND fad.pk1_value=to_char(plx.from_line_id)

AND plx.from_line_id IS NOT NULL)

(fad.entity_name='PO_HEADERS' AND fad.pk1_value=to_char(plx.contract_id)

AND plx.contract_id IS NOT NULL)

(fad.entity_name='MTL_SYSTEM_ITEMS' AND

fad.pk1_value=to_char(PO_COMMUNICATION_PVT.getInventoryOrgId()) AND

fad.pk2_value=to_char(plx.item_id) AND plx.item_id is not null)

)

AND fad.datatype_id=6

AND fad.function_name='PO_PRINTPO'

order by fad.seq_num) AS line_file_attachments,

CURSOR(SELECT pllx.*, CURSOR(SELECT zl.tax_rate_id tax_code_id, zl.tax_rate_code tax_name

, zl.TAX_RATE

, zl.TAX_RATE_TYPE

, zl.ENTITY_CODE

, zl.EVENT_CLASS_CODE

, zl.TRX_ID

, zl.APPLICATION_ID

, zl.TRX_LEVEL_TYPE

, zl.TRX_LINE_ID

, zl.TRX_NUMBER

, zl.TRX_LINE_NUMBER

, zl.TAX_LINE_NUMBER

, zl.TAX_REGIME_CODE

, zl.TAX

, zl.TAX_APPORTIONMENT_LINE_NUMBER

, zl.UNIT_PRICE

, zl.LINE_AMT

, zl.TRX_LINE_QUANTITY

, zl.UNROUNDED_TAXABLE_AMT

, zl.UNROUNDED_TAX_AMT

, zl.TAX_CURRENCY_CODE

, zl.TAX_AMT

, zl.TAX_AMT_TAX_CURR

, zl.TAX_AMT_FUNCL_CURR

, zl.TAXABLE_AMT_TAX_CURR

, zl.TAXABLE_AMT_FUNCL_CURR

, zl.TAX_LINE_ID

, zl.TAX_STATUS_CODE

, zl.TAX_JURISDICTION_CODE

, zl.TAXABLE_AMT

, zl.TAX_TYPE_CODE

FROM zx_lines zl

WHERE zl.application_id = 201

AND zl.entity_code = 'PURCHASE_ORDER'

AND zl.event_class_code = 'PO_PA'

AND zl.trx_id = pllx.po_header_id

AND zl.trx_line_id = pllx.line_location_id

) AS tax_names,CURSOR( SELECT pllx.line_location_id, fds.short_text

FROM

fnd_attached_docs_form_vl fad,

fnd_documents_short_text fds

WHERE entity_name = 'PO_SHIPMENTS' AND

pk1_value = to_char(pllx.line_location_id) AND

function_name = 'PO_PRINTPO'

AND fad.media_id = fds.media_id

AND fad.datatype_id=1

order by fad.seq_num) AS line_loc_short_text,CURSOR(

SELECT fad.url web_page --Bug#4958642

FROM fnd_attached_docs_form_vl fad

WHERE fad.entity_name='PO_SHIPMENTS'

AND fad.pk1_value=to_char(pllx.line_location_id)

AND fad.datatype_id=5

AND fad.function_name='PO_PRINTPO'

order by fad.seq_num ) AS line_loc_url_attachments,CURSOR(

SELECT fad.file_name

FROM fnd_attached_docs_form_vl fad

WHERE fad.entity_name='PO_SHIPMENTS'

AND fad.pk1_value=to_char(pllx.line_location_id)

AND fad.datatype_id=6

AND fad.function_name='PO_PRINTPO'

order by fad.seq_num) AS line_loc_file_attachments,

CURSOR(SELECT pdx.* FROM po_distribution_xml pdx WHERE pdx.draft_id = -1 AND pdx.po_header_id = phx.po_header_id and pdx.LINE_LOCATION_ID = pllx.LINE_LOCATION_ID) AS distributions

FROM po_line_locations_xml pllx WHERE pllx.draft_id = -1 AND pllx.po_line_id = plx.po_line_id AND NVL(pllx.payment_type,'NONE') NOT IN ('ADVANCE','DELIVERY') order by pllx.shipment_num ) AS line_locations

FROM po_lines_xml plx WHERE plx.draft_id = -1 AND plx.po_header_id = phx.po_header_id

and nvl(plx.cancel_flag, 'N') = decode(PO_COMMUNICATION_PVT.getWithCanceledLines(), 'N', 'N', nvl(plx.cancel_flag, 'N'))

and PO_COMMUNICATION_PVT.getWithClosedLines() = decode(instr(nvl(plx.closed_code, ' '), 'CLOSED'), 0, PO_COMMUNICATION_PVT.getWithClosedLines(), 'Y') order by plx.line_num) AS lines

FROM PO_HEADERS_XML phx WHERE phx.draft_id = -1 AND phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND

phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()

Oracle EBS Purchase Order Cancel API

--------Approved Purchase Order Cancel via API

DECLARE

l_return_status VARCHAR2 (50);

CURSOR C_PO_CANCEL

SELECT pha.po_header_id,

pha.org_id,

pha.segment1 po_number,

pha.type_lookup_code,

pha.cancel_flag,

pha.closed_code

FROM po_headers_all pha

WHERE 1=1

AND pha.segment1 = '2100001262' -- Enter PO Number

AND nvl(pha.closed_code,'OPEN') = 'OPEN'

AND nvl(pha.cancel_flag, 'N') = 'N'

AND approved_flag = 'Y';

BEGIN

fnd_global.apps_initialize (user_id => 1278,

resp_id => 50817,

resp_appl_id => 7000);

FOR i IN c_po_cancel

LOOP

mo_global.init ('PO');

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

DBMS_OUTPUT.PUT_LINE ('Calling API PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT For Cancelling Documents');

po_document_control_pub.control_document

(p_api_version => 1.0, -- p_api_version

p_init_msg_list => fnd_api.g_true, -- p_init_msg_list

p_commit => fnd_api.g_true, -- p_commit

x_return_status => l_return_status, -- x_return_status

p_doc_type => 'PO', -- p_doc_type

p_doc_subtype => 'STANDARD', -- p_doc_subtype

p_doc_id => i.po_header_id, -- p_doc_id

p_doc_num => NULL, -- p_doc_num

p_release_id => NULL, -- p_release_id

p_release_num => NULL, -- p_release_num

p_doc_line_id => NULL, -- p_doc_line_id

p_doc_line_num => NULL, -- p_doc_line_num

p_doc_line_loc_id => NULL, -- p_doc_line_loc_id

p_doc_shipment_num => NULL, -- p_doc_shipment_num

p_action => 'CANCEL', -- p_action

p_action_date => SYSDATE, -- p_action_date

p_cancel_reason => NULL, -- p_cancel_reason

p_cancel_reqs_flag => 'N', -- p_cancel_reqs_flag

p_print_flag => NULL, -- p_print_flag

p_note_to_vendor => NULL, -- p_note_to_vendor

p_use_gldate =>NULL ,

p_org_id => i.org_id

);

COMMIT;

DBMS_OUTPUT.PUT_LINE('The Return Status of the API is => ' ||l_return_status);

If l_return_status = 'S' Then

DBMS_OUTPUT.PUT_LINE('The Purchase Order Which is Cancelled Now => ' ||i.po_number);

Else

DBMS_OUTPUT.PUT_LINE('The Purchase Order =>' ||i.po_number|| 'Failed for Cancel Due To Following Reason:');

-- Error messages returned by the Cancel API

FOR j IN 1 .. fnd_msg_pub.count_msg

LOOP

DBMS_OUTPUT.put_line (fnd_msg_pub.get (p_msg_index => j,

p_encoded => 'F'));

END LOOP;

END IF;

END LOOP;

END;

Output:

Calling API PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT For Cancelling Documents
The Return Status of the API is => S
The Purchase Order Which is Cancelled Now => 2100001262

Projectwise PR to Purchase Order Details Script

Projectwise PR to PO Details Report Query

Select MA.*,TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') SYS_DATE FROM

(SELECT MAIN.project_no,

MAIN.Project_name,

MAIN.PR_Number,

MAIN.PR_Creation_date,

MAIN.Item_Code,

MAIN.ITEM_DESCRIPTION,

MAIN.ORG_ID,

organization_id,

MAIN.PO_HEADER_ID,

MAIN.PO_line_ID,

MAIN.PO_Number,

MAIN.PO_Date,

MAIN.PO_Status, DECODE(MAIN.VENDOR_NAME,NULL,'ALL',MAIN.VENDOR_NAME,MAIN.VENDOR_NAME) VN,

MAIN.Vendor_Code,

MAIN.Vendor_name,

MAIN.UNIT_MEAS_LOOKUP_CODE,

MAIN.QUANTITY PR_QTY,

MAIN.PO_Quantity,

MAIN.QUANTITY_RECEIVED,

MAIN.QUANTITY_ACCEPTED,

MAIN.QUANTITY_REJECTED,

MAIN.QUANTITY_BILLED,

MAIN.Balance_Quantity,

MAIN.invoice_id,

MAIN.INVOICE_NUMBER,

MAIN.invoice_date,

MAIN.invoice_amount,

MAIN.INVOICE_DOC,

MAIN.INV_QTY,

MAIN.DOC_NO,

MAIN.payment_date,

MAIN.payment_amount

FROM

(

Select

(select SEGMENT1 from pa_projects_all where project_id = prd.project_id) Project_no,

(select NAME from pa_projects_all where project_id = prd.project_id) Project_name,

prh.segment1 pr_number,

prh.creation_date pr_creation_date,

(select REPLACE(REPLACE(REPLACE(REPLACE(msi.segment1,CHR(13),NULL),CHR(10),NULL),CHR(9),NULL),'"',null)

from mtl_system_items_b msi

where msi.inventory_item_id = prl.item_id AND msi.organization_id = poll.ship_to_organization_id) Item_Code,

prl.item_description,

prl.quantity,

poh.org_id,

poll.ship_to_organization_id organization_id,

poh.po_header_id,

pol.po_line_id,

poh.segment1 po_number,

poh.creation_date po_date,

DECODE(poh.authorization_status,NULL,'IN COMPLETE',poh.authorization_status) po_status,

(select segment1 from ap_suppliers where vendor_id=poh.vendor_id) Vendor_Code ,

(select vendor_name from ap_suppliers where vendor_id = poh.vendor_id) vendor_name,

pol.unit_meas_lookup_code,

poll.quantity po_quantity,

poll.quantity_received,

poll.quantity_accepted,

poll.quantity_rejected,

poll.quantity_billed,

--(poll.quantity - poll.QUANTITY_ACCEPTED) Balance_Quantity, comment by JG

(poll.quantity - decode(poll.quantity_accepted,0,poll.QUANTITY_BILLED,poll.quantity_accepted)) Balance_Quantity,

aia.invoice_id,

aia.invoice_num invoice_number,

aia.invoice_date invoice_date,

aia.invoice_amount invoice_amount,

aia.doc_sequence_value invoice_doc,

--(Select sum(quantity_invoiced) FROM ap_invoice_lines_all WHERE line_type_lookup_code ='ITEM'

-- AND org_id=aia.org_id AND invoice_id=aia.invoice_id AND PO_LINE_LOCATION_ID = poll.LINE_LOCATION_ID) INV_QTY,

invoice_qty INV_QTY,

(Select ac.doc_sequence_value FROM apps.ap_checks_all ac Where aip.check_id = ac.check_id

AND aip.org_id = ac.org_id) doc_no,

(Select ac.check_date FROM apps.ap_checks_all ac Where aip.check_id = ac.check_id

AND aip.org_id = ac.org_id) payment_date,

(Select ac.amount FROM apps.ap_checks_all ac Where aip.check_id = ac.check_id

AND aip.org_id = ac.org_id) payment_amount

FROM

po_requisition_headers_all prh

,po_requisition_lines_all prl

,po_req_distributions_all prd

,po_headers_all poh

,po_lines_all pol

,po_distributions_all pod

,po_line_locations_All poll

,(Select org_id,invoice_id,NVL(po_header_id,quick_po_header_id)po_header_id,invoice_num,invoice_date,invoice_amount,doc_sequence_value From ap_invoices_all) aia

,(Select org_id,invoice_id,po_header_id,po_line_id,sum(quantity_invoiced)invoice_qty From ap_invoice_lines_all Group by org_id,invoice_id,po_header_id,po_line_id) aila

,(Select org_id,invoice_id,check_id From apps.ap_invoice_payments_all) aip

WHERE

prh.requisition_header_id = prl.requisition_header_id

AND prl.requisition_line_id = prd.requisition_line_id

and prd.distribution_id = pod.req_distribution_id

and poh.po_header_id = pod.po_header_id

and poh.po_header_id = pol.po_header_id

and poh.PO_HEADER_ID = poll.PO_HEADER_ID

and pol.PO_LINE_ID = pod.PO_LINE_ID

and pod.line_location_id = poll.line_location_id

and prh.authorization_status IN ('APPROVED','PRE-APPROVED')

AND aia.po_header_id(+) = poh.po_header_id

AND aia.org_id(+) = poh.org_id

AND aia.invoice_id = aila.invoice_id

AND aia.org_id = aila.org_id

AND pol.po_line_id = aila.po_line_id(+)

AND aia.invoice_id = aip.invoice_id

AND aia.org_id = aip.org_id

AND prd.project_id = NVL(:p_project_name,prd.project_id)

AND prh.creation_date BETWEEN NVL(:P_START_DATE,prh.creation_date) AND NVL(:P_END_DATE,prh.creation_date)

AND prh.segment1 = NVL(:P_REQ_NUM,prh.segment1)

AND prl.item_id = NVL(:P_ITEM,prl.item_id)

UNION------------------------

SELECT

(select segment1 from pa_projects_all where project_id = prd.project_id) project_no,

(select name from pa_projects_all where project_id = prd.project_id) project_name,

prh.segment1 pr_number,

prh.creation_date pr_creation_date,

(select replace(replace(replace(replace(msi.segment1,chr(13),null),chr(10),null),chr(9),null),'"',null)

from mtl_system_items_b msi

where msi.inventory_item_id = prl.item_id and msi.organization_id = prl.destination_organization_id

) item_code,

prl.item_description,

prl.quantity,

poh.org_id,

poll.ship_to_organization_id organization_id,

poh.po_header_id,

pol.po_line_id,

poh.segment1 po_number,

poh.creation_date po_date,

poh.authorization_status po_status,

(select segment1 from ap_suppliers where vendor_id=prl.vendor_id) vendor_code ,

(select vendor_name from ap_suppliers where vendor_id=prl.vendor_id) vendor_name,

pol.unit_meas_lookup_code,

poll.quantity po_quantity,

poll.quantity_received,

poll.quantity_accepted,

poll.quantity_rejected,

poll.quantity_billed,

(poll.quantity - poll.quantity_accepted) balance_quantity,

null invoice_id,

null invoice_number,

null invoice_date,

null invoice_amount,

null invoice_doc,

null inv_qty,

null doc_no,

null payment_date,

null payment_amount

FROM po_requisition_headers_all prh,

po_requisition_lines_all prl,

po_req_distributions_all prd,

po_line_locations_all poll,

po_lines_all pol,

po_headers_all poh

WHERE prh.requisition_header_id = prl.requisition_header_id

AND prl.requisition_line_id = prd.requisition_line_id

AND poll.line_location_id(+) = prl.line_location_id

AND poll.po_header_id = poh.po_header_id(+)

AND poll.po_line_id = pol.po_line_id(+)

AND prh.authorization_status = 'APPROVED'

AND poll.line_location_id IS NULL

AND prl.closed_code IS NULL

AND NVL (prl.cancel_flag, 'N') <> 'Y'

AND prd.project_id = NVL(:p_project_name,prd.project_id)

AND prh.creation_date BETWEEN NVL(:P_START_DATE,prh.creation_date) AND NVL(:P_END_DATE,prh.creation_date)

AND prh.segment1 = NVL(:P_REQ_NUM,prh.segment1)

AND prl.item_id = NVL(:P_ITEM,prl.item_id)

)MAIN

WHERE 1=1--MAIN.PROJECT_NAME = DECODE(:P_PROJECT_NAME,NULL,PROJECT_NAME,:P_PROJECT_NAME)

--AND MAIN.PR_CREATION_DATE BETWEEN NVL(:P_START_DATE,PR_CREATION_DATE) AND NVL(:P_END_DATE,PR_CREATION_DATE)

--AND MAIN.PR_NUMBER = NVL(:P_REQ_NUM,MAIN.PR_NUMBER)

--AND MAIN.Item_Code = NVL(:P_ITEM,MAIN.Item_Code)

ORDER BY 1, 3,9,10) MA

WHERE MA.VN =NVL(:P_VENDOR_NAME,MA.VN)

Pocket Hanuman Chalisa (Hindi)

PO and OM Joining

Select *
FROM rcv_transactions_interface rti,
          rcv_shipment_lines rsl,
          rcv_shipment_headers rsh,
          po_requisition_lines prl,
          mtl_system_items msi,
          mtl_units_of_measure mum1,
          mtl_units_of_measure mum2,
          mtl_units_of_measure mum3,
          mtl_units_of_measure mum4,
          mtl_units_of_measure mum5,
          po_lookup_codes plc1,
          po_lookup_codes plc2,
          po_lookup_codes plc3,
          po_lookup_codes plc4,
          po_lookup_codes plc5,
          po_lookup_codes plc6,
          oe_sold_to_orgs_v oec,
          oe_order_lines_all oel,
          oe_order_headers_all oeh,
          oe_transaction_types_tl oetl,
          oe_transaction_types_all oet,
          po_headers_trx_v ph,
          po_lines_trx_v pl,
          po_line_locations_trx_v pll,
          po_releases_all pr,
          po_vendors pov,
          hr_locations_all_tl hrl,
          mtl_transaction_reasons mtr,
          rcv_routing_headers rrh,
          per_all_people_f hre,
          org_organization_definitions ood,
          fnd_currencies_vl fcv,
          per_jobs pj,
          po_job_associations pja
    WHERE (    rti.transaction_type = plc1.lookup_code
           AND plc1.lookup_type = 'RCV TRANSACTION TYPE'
          )
      AND (rti.shipment_line_id = rsl.shipment_line_id(+))
      AND (rti.requisition_line_id = prl.requisition_line_id(+))
      AND (rti.shipment_header_id = rsh.shipment_header_id(+))
      AND (    msi.inventory_item_id(+) = rti.item_id
           AND NVL (msi.organization_id, rti.to_organization_id) =
                                                        rti.to_organization_id
           AND msi.primary_unit_of_measure = mum1.unit_of_measure(+)
          )
      AND (rti.unit_of_measure = mum2.unit_of_measure(+))
      AND (plc2.lookup_type(+) = 'INSPECTION STATUS'
           AND plc2.lookup_code(+) = rti.inspection_status_code)
      AND oel.line_id(+) = rti.oe_order_line_id
      AND oeh.header_id(+) = rti.oe_order_header_id
      AND oec.customer_id(+) = rsh.customer_id
      AND oet.transaction_type_id(+) = oeh.order_type_id
      AND oet.transaction_type_id = oetl.transaction_type_id(+)
      AND oetl.LANGUAGE(+) = USERENV ('LANG')
      AND oet.transaction_type_code(+) = 'ORDER'
      AND ph.po_header_id(+) = rti.po_header_id
      AND (pl.po_line_id(+) = rti.po_line_id)
      AND (pll.line_location_id(+) = rti.po_line_location_id)
      AND (pr.po_release_id(+) = rti.po_release_id)
      AND pov.vendor_id(+) = rsh.vendor_id
      AND plc3.lookup_type(+) = 'RCV DESTINATION TYPE'
      AND plc3.lookup_code(+) = rti.destination_type_code
      AND hrl.location_id(+) =
                             NVL (rti.deliver_to_location_id, rti.location_id)
      AND hrl.LANGUAGE(+) = USERENV ('LANG')
      AND rrh.routing_header_id(+) = rti.routing_header_id
      AND mum3.unit_of_measure(+) = pl.unit_meas_lookup_code
      AND mum4.unit_of_measure(+) = pll.secondary_unit_of_measure
      AND mum5.uom_code(+) = oel.ordered_quantity_uom2
      AND mtr.reason_id(+) = rti.reason_id
      AND hre.person_id(+) = rti.deliver_to_person_id
      AND TRUNC (SYSDATE) BETWEEN hre.effective_start_date(+) AND hre.effective_end_date(+)
      AND DECODE (hr_security.view_all,
                  'Y', 'TRUE',
                  hr_security.show_person (hre.person_id(+), hre.current_applicant_flag(+),
                                           hre.current_employee_flag(+), hre.current_npw_flag(+),
                                           hre.employee_number(+), hre.applicant_number(+),
                                           hre.npw_number(+))
                 ) = 'TRUE'
      AND DECODE (hr_general.get_xbg_profile,
                  'Y', hre.business_group_id(+),
                  hr_general.get_business_group_id
                 ) = hre.business_group_id(+)
      AND ood.organization_id(+) = rti.from_organization_id
      AND DECODE (rti.source_document_code,
                  'PO', 'PO TYPE',
                  'SHIPMENT SOURCE TYPE'
                 ) = plc4.lookup_type
      AND DECODE (rti.transaction_type,
                  'UNORDERED', 'STANDARD',
                  DECODE (rti.source_document_code,
                          'PO', ph.type_lookup_code,
                          rsh.receipt_source_code
                         )
                 ) = plc4.lookup_code
      AND plc5.lookup_type = 'TRANSACTION STATUS'
      AND plc5.lookup_code = rti.transaction_status_code
      AND plc6.lookup_type = 'RCV PROCESSING MODE'
      AND plc6.lookup_code = rti.processing_mode_code
      AND fcv.currency_code(+) = rti.currency_code
      AND pj.job_id(+) = rti.job_id
      AND pja.job_id(+) = rti.job_id
      AND NVL (ph.org_id, -99) = NVL (pll.org_id, -99);

Oracle R12 PO Approval all Approved Query

SELECT fu.user_name po_created_by,pha.org_id ,
(SELECT NAME || ' ( ' || short_code || ' )'
FROM hr_operating_units
WHERE organization_id = pha.org_id) ou_name,
pha.segment1 po_num,pha.revision_num, pha.approved_date,SUM (pla.quantity) po_qty, SUM (pla.unit_price) po_price,
--SUM(pla.quantity*pla.unit_price) po_value,
pha.authorization_status, pha.creation_date po_creation_date,
wn.to_user, wn.subject, wn.original_recipient, wn.begin_date,
TRUNC (SYSDATE - wn.begin_date) no_days_pending
,d.name approval_path,wn.from_role approve_by,pha.po_header_id,wn.notification_id,pha.agent_id
FROM wf_notifications wn,
po_headers_all pha,
fnd_user fu,
po_lines_all pla
,po_action_history a,per_position_structures_v d
WHERE 1=1
AND pha.org_id = pla.org_id
AND pha.authorization_status = 'APPROVED'
AND notification_id =(Select MAX(notification_id) FROM wf_notifications wn Where pha.segment1 = user_key )
AND a.SEQUENCE_NUM = (Select max(aa.sequence_num) FROM po_action_history aa where aa.object_id = a.object_id and ACTION_CODE='SUBMIT' )
-- AND MESSAGE_TYPE = 'POAPPRV'
-- AND message_name = 'PO_PO_APPROVE_PDF'
AND a.object_id = pha.po_header_id
AND a.approval_path_id = d.position_structure_id
--AND wn.end_date IS NULL
AND pha.segment1 = user_key
AND pha.created_by = fu.user_id
AND pha.po_header_id = pla.po_header_id
AND NVL (pha.cancel_flag, 'N') <> 'Y'
AND pha.org_id = NVL(:P_Org_id,pha.org_id)
AND pha.agent_id = NVL((select employee_id from fnd_user where user_id=:p_buyer_id),pha.agent_id)
--AND trunc(a.action_date) >= '08-JAN-2019'
---AND trunc(pha.approved_date) between trunc(sysdate-1) and trunc(sysdate)
----AND trunc(wn.begin_date) between trunc(:P_from_date) and trunc(:P_to_date)
--AND NOT EXISTS (SELECT 1 FROM po_action_history pah, per_position_structures_v pps WHERE pps.name LIKE '%Emergency%'
AND pah.SEQUENCE_NUM=(Select max(aa.sequence_num) FROM po_action_history aa where aa.object_id = pah.object_id and ACTION_CODE='SUBMIT')
AND pah.approval_path_id = pps.position_structure_id AND pha.po_header_id = pah.object_id)
GROUP BY pha.authorization_status,
pha.creation_date,pha.approved_date,
pha.org_id,
wn.to_user,
wn.subject,
wn.original_recipient,
fu.user_name,
wn.begin_date,d.name,pha.agent_id,
pha.segment1,pha.revision_num,wn.from_role, pha.po_header_id, wn.notification_id
ORDER BY 1, 2

Oracle R12 PO Approval Pending Query

Oracle R12 PO Approval Pending Query

SELECT fu.user_name po_created_by,pha.org_id ,
(SELECT NAME || ' ( ' || short_code || ' )'
FROM hr_operating_units
WHERE organization_id = pha.org_id) ou_name,
pha.segment1 po_num, pha.revision_num, SUM(pla.quantity) po_qty, SUM(pla.unit_price) po_price,
pha.authorization_status, pha.creation_date po_creation_date,
wn.to_user, wn.subject, wn.original_recipient, wn.begin_date,
TRUNC (SYSDATE - wn.begin_date) no_days_pending,d.name Approval_path
,wn.from_role forrwad_by,wn.notification_id,pha.po_header_id
FROM wf_notifications wn,
po_headers_all pha,
fnd_user fu,
po_lines_all pla,
po_action_history a,per_position_structures_v d
WHERE 1=1 --status = 'OPEN'
AND pha.org_id = pla.org_id
AND pha.authorization_status = 'IN PROCESS'
-- AND MESSAGE_TYPE = 'POAPPRV'
-- AND message_name = 'PO_PO_APPROVE_PDF'
AND a.object_id = pha.po_header_id
AND a.approval_path_id = d.position_structure_id
--AND wn.end_date IS NULL
AND NOTIFICATION_ID =(Select max(NOTIFICATION_ID) FROM wf_notifications wn Where pha.segment1 = user_key)
AND a.SEQUENCE_NUM = (Select max(aa.sequence_num) FROM po_action_history aa where aa.object_id = a.object_id and ACTION_CODE='SUBMIT' )
AND pha.segment1 = user_key
AND pha.created_by = fu.user_id
AND pha.po_header_id = pla.po_header_id
AND NVL (pha.cancel_flag, 'N') <> 'Y'
AND pha.org_id = NVL(:P_ORG_ID,pha.org_id)
---- AND pha.creation_date >= '08-JAN-2019'
----AND trunc(wn.begin_date) between trunc(:P_from_date) and trunc(:P_to_date)
AND pha.agent_id = NVL((select employee_id from fnd_user where user_id=:p_buyer_id),pha.agent_id)
--AND NOT EXISTS (SELECT 1 FROM po_action_history pah, per_position_structures_v pps WHERE pps.name LIKE '%Emergency%'
AND pah.SEQUENCE_NUM=(Select max(aa.sequence_num) FROM po_action_history aa where aa.object_id = pah.object_id and ACTION_CODE='SUBMIT')
AND pah.approval_path_id = pps.position_structure_id AND pha.po_header_id = pah.object_id)
GROUP BY pha.authorization_status,
pha.creation_date,
pha.org_id,
wn.to_user,
wn.subject,
wn.original_recipient,
fu.user_name,
wn.begin_date,d.name,
wn.from_role,wn.notification_id,pha.revision_num,
pha.po_header_id,pha.segment1
ORDER BY 1, 2

Oracle EBS PO Position Hierarchy Query

SELECT NAME PATH_NAME,PARENT_NAME FROM
(SELECT     pps.position_structure_id,pps.NAME, LPAD (' ', 5 * LEVEL) || has.NAME HIERARCHY,
              has.position_id, LEVEL rep_level, hap.NAME parent_name,
              pse.parent_position_id, has.NAME child_name,
              pse.subordinate_position_id
         FROM (SELECT NAME, position_id
                 FROM hr_all_positions_f_tl
                WHERE LANGUAGE = USERENV ('LANG')) hap,
              (SELECT NAME, position_id
                 FROM hr_all_positions_f_tl
                WHERE LANGUAGE = USERENV ('LANG')) has,
              per_pos_structure_elements pse,
              per_pos_structure_versions pve,
              per_position_structures pps
        WHERE pse.business_group_id = 0
          AND pve.position_structure_id = pps.position_structure_id
          AND pse.pos_structure_version_id = pve.pos_structure_version_id
          AND SYSDATE BETWEEN pve.date_from AND NVL (pve.date_to, SYSDATE)
          AND hap.position_id = pse.parent_position_id
          AND has.position_id = pse.subordinate_position_id
   START WITH pse.parent_position_id =
                 (SELECT parent_position_id
                    FROM per_pos_structure_elements a
                   WHERE a.pos_structure_version_id = pse.pos_structure_version_id
                     AND a.pos_structure_element_id =
                            (SELECT MIN (pos_structure_element_id)
                               FROM per_pos_structure_elements b
                              WHERE b.pos_structure_version_id =
                                                    a.pos_structure_version_id))
   CONNECT BY PRIOR pse.subordinate_position_id = pse.parent_position_id
          AND PRIOR pse.pos_structure_version_id = pse.pos_structure_version_id
          AND PRIOR pse.business_group_id = pse.business_group_id)
WHERE position_structure_id=
(Select approval_path_id From po_action_history pha where object_id= :po_header_id and sequence_num=
(Select max(sequence_num) From po_action_history pha where object_id= :po_header_id) And ROWNUM=1)
GROUP BY NAME,PARENT_NAME;

Purchasing

PO

SELECT fu.user_name po_created_by,pha.org_id ,
(SELECT NAME || ' ( ' || short_code || ' )'
FROM hr_operating_units
WHERE organization_id = pha.org_id) ou_name,
pha.segment1 po_num,pha.revision_num, pha.approved_date,SUM (pla.quantity) po_qty, SUM (pla.unit_price) po_price,
--SUM(pla.quantity*pla.unit_price) po_value,
pha.authorization_status, pha.creation_date po_creation_date,
wn.to_user, wn.subject, wn.original_recipient, wn.begin_date,
TRUNC (SYSDATE - wn.begin_date) no_days_pending
,d.name approval_path,wn.from_role approve_by,pha.po_header_id,wn.notification_id,pha.agent_id
FROM wf_notifications wn,
po_headers_all pha,
fnd_user fu,
po_lines_all pla
,po_action_history a,per_position_structures_v d
WHERE 1=1
AND pha.org_id = pla.org_id
AND pha.authorization_status = 'APPROVED'
AND notification_id =(Select MAX(notification_id) FROM wf_notifications wn Where pha.segment1 = user_key )
AND a.SEQUENCE_NUM = (Select max(aa.sequence_num) FROM po_action_history aa where aa.object_id = a.object_id and ACTION_CODE='SUBMIT' )
-- AND MESSAGE_TYPE = 'POAPPRV'
-- AND message_name = 'PO_PO_APPROVE_PDF'
AND a.object_id = pha.po_header_id
AND a.approval_path_id = d.position_structure_id
--AND wn.end_date IS NULL
AND pha.segment1 = user_key
AND pha.created_by = fu.user_id
AND pha.po_header_id = pla.po_header_id
AND NVL (pha.cancel_flag, 'N') <> 'Y'
AND pha.org_id = NVL(:P_Org_id,pha.org_id)
AND pha.agent_id = NVL((select employee_id from fnd_user where user_id=:p_buyer_id),pha.agent_id)
--AND trunc(a.action_date) >= '08-JAN-2019'
---AND trunc(pha.approved_date) between trunc(sysdate-1) and trunc(sysdate)
----AND trunc(wn.begin_date) between trunc(:P_from_date) and trunc(:P_to_date)
--AND NOT EXISTS (SELECT 1 FROM po_action_history pah, per_position_structures_v pps WHERE pps.name LIKE '%Emergency%'
AND a.SEQUENCE_NUM=(Select max(aa.sequence_num) FROM po_action_history aa where aa.object_id = a.object_id and ACTION_CODE='SUBMIT')
-- AND pah.approval_path_id = pps.position_structure_id AND pha.po_header_id = pah.object_id
GROUP BY pha.authorization_status,
pha.creation_date,pha.approved_date,
pha.org_id,
wn.to_user,
wn.subject,
wn.original_recipient,
fu.user_name,
wn.begin_date,d.name,pha.agent_id,
pha.segment1,pha.revision_num,wn.from_role, pha.po_header_id, wn.notification_id
ORDER BY 1, 2

Oracle Purchasing- PO Match Approval Level column store

In Oracle Purchasing Match options column store in INSPECTION_REQUIRED_FLAG and RECEIPT_REQUIRED_FLAG fields of table PO_LINE_LOCATIONS_ALL.

2-way matching
--------------------
INSPECTION_REQUIRED_FLAG = 'N' and RECEIPT_REQUIRED_FLAG = 'N'

3-way matching
---------------------
INSPECTION_REQUIRED_FLAG = 'N' and RECEIPT_REQUIRED_FLAG = 'Y'

4-way matching
---------------------
INSPECTION_REQUIRED_FLAG = 'Y' and RECEIPT_REQUIRED_FLAG = 'Y'

MATCH_OPTION fields

---------------------------------

P Match to Purchase Order
R Match to Receipt