AP_DISTRIBUTION_MATCH

Details

Schema: FUSION
Object owner: AP
Object type: VIEW

Columns

Name
ORG_ID PO_DISTRIBUTION_ID PO_HEADER_ID PO_LINE_ID REQUESTOR DISTRIBUTION_TYPE MATCHING_BASIS MATCH_OPTION SHIPMENT_UNIT_PRICE DELIVERED BILLED ORDERED QUANTITY_ORDERED QUANTITY_BILLED QUANTITY_CANCELLED QUANTITY_DELIVERED QUANTITY_FINANCED QUANTITY_RECOUPED AMOUNT_ORDERED AMOUNT_BILLED AMOUNT_CANCELLED AMOUNT_DELIVERED AMOUNT_FINANCED AMOUNT_RECOUPED VALUE_BASIS_DISTRIBUTION ACCRUE_ON_RECEIPT_FLAG CODE_COMBINATION_ID DELIVER_TO_LOCATION_ID DISTRIBUTION_NUM LINE_LOCATION_ID ALLOW_FLEX_OVERRIDE_FLAG PURCH_ENCUMBRANCE_FLAG TRANSFER_DESC_FLEX_FLAG PJC_EXPENDITURE_TYPE_ID PJC_EXPENDITURE_ITEM_DATE PJC_ORGANIZATION_ID PJC_PROJECT_ID PJC_TASK_ID PA_ADDITION_FLAG VARIANCE_ACCOUNT_ID ATTRIBUTE1 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE_CATEGORY PO_UOM PJC_CONTEXT_CATEGORY PJC_BILLABLE_FLAG PJC_USER_DEF_ATTRIBUTE1 PJC_USER_DEF_ATTRIBUTE2 PJC_USER_DEF_ATTRIBUTE3 PJC_USER_DEF_ATTRIBUTE4 PJC_USER_DEF_ATTRIBUTE5 PJC_USER_DEF_ATTRIBUTE6 PJC_USER_DEF_ATTRIBUTE7 PJC_USER_DEF_ATTRIBUTE8 PJC_USER_DEF_ATTRIBUTE9 PJC_USER_DEF_ATTRIBUTE10 PJC_CONTRACT_ID PJC_WORK_TYPE_ID PJC_CAPITALIZABLE_FLAG PJC_RESERVED_ATTRIBUTE1 PJC_RESERVED_ATTRIBUTE2 PJC_RESERVED_ATTRIBUTE3 PJC_RESERVED_ATTRIBUTE4 PJC_RESERVED_ATTRIBUTE5 PJC_RESERVED_ATTRIBUTE6 PJC_RESERVED_ATTRIBUTE7 PJC_RESERVED_ATTRIBUTE8 PJC_RESERVED_ATTRIBUTE9 PJC_RESERVED_ATTRIBUTE10 PJC_FUNDING_ALLOCATION_ID PJC_CONTRACT_LINE_ID BUILD_PREPAYMENT_ACCOUNTS_FLAG PREPAY_CODE_COMBINATION_ID CREDIT_FLAG

Name

ORG_ID

PO_DISTRIBUTION_ID

PO_HEADER_ID

PO_LINE_ID

REQUESTOR

DISTRIBUTION_TYPE

MATCHING_BASIS

MATCH_OPTION

SHIPMENT_UNIT_PRICE

DELIVERED

BILLED

ORDERED

QUANTITY_ORDERED

QUANTITY_BILLED

QUANTITY_CANCELLED

QUANTITY_DELIVERED

QUANTITY_FINANCED

QUANTITY_RECOUPED

AMOUNT_ORDERED

AMOUNT_BILLED

AMOUNT_CANCELLED

AMOUNT_DELIVERED

AMOUNT_FINANCED

AMOUNT_RECOUPED

VALUE_BASIS_DISTRIBUTION

ACCRUE_ON_RECEIPT_FLAG

CODE_COMBINATION_ID

DELIVER_TO_LOCATION_ID

DISTRIBUTION_NUM

LINE_LOCATION_ID

ALLOW_FLEX_OVERRIDE_FLAG

PURCH_ENCUMBRANCE_FLAG

TRANSFER_DESC_FLEX_FLAG

PJC_EXPENDITURE_TYPE_ID

PJC_EXPENDITURE_ITEM_DATE

PJC_ORGANIZATION_ID

PJC_PROJECT_ID

PJC_TASK_ID

PA_ADDITION_FLAG

VARIANCE_ACCOUNT_ID

ATTRIBUTE1

ATTRIBUTE10

ATTRIBUTE11

ATTRIBUTE12

ATTRIBUTE13

ATTRIBUTE14

ATTRIBUTE15

ATTRIBUTE2

ATTRIBUTE3

ATTRIBUTE4

ATTRIBUTE5

ATTRIBUTE6

ATTRIBUTE7

ATTRIBUTE8

ATTRIBUTE9

ATTRIBUTE_CATEGORY

PO_UOM

PJC_CONTEXT_CATEGORY

PJC_BILLABLE_FLAG

PJC_USER_DEF_ATTRIBUTE1

PJC_USER_DEF_ATTRIBUTE2

PJC_USER_DEF_ATTRIBUTE3

PJC_USER_DEF_ATTRIBUTE4

PJC_USER_DEF_ATTRIBUTE5

PJC_USER_DEF_ATTRIBUTE6

PJC_USER_DEF_ATTRIBUTE7

PJC_USER_DEF_ATTRIBUTE8

PJC_USER_DEF_ATTRIBUTE9

PJC_USER_DEF_ATTRIBUTE10

PJC_CONTRACT_ID

PJC_WORK_TYPE_ID

PJC_CAPITALIZABLE_FLAG

PJC_RESERVED_ATTRIBUTE1

PJC_RESERVED_ATTRIBUTE2

PJC_RESERVED_ATTRIBUTE3

PJC_RESERVED_ATTRIBUTE4

PJC_RESERVED_ATTRIBUTE5

PJC_RESERVED_ATTRIBUTE6

PJC_RESERVED_ATTRIBUTE7

PJC_RESERVED_ATTRIBUTE8

PJC_RESERVED_ATTRIBUTE9

PJC_RESERVED_ATTRIBUTE10

PJC_FUNDING_ALLOCATION_ID

PJC_CONTRACT_LINE_ID

BUILD_PREPAYMENT_ACCOUNTS_FLAG

PREPAY_CODE_COMBINATION_ID

CREDIT_FLAG

Query

SQL_Statement
SELECT ph.billto_bu_id org_id , pd.po_distribution_id po_distribution_id , pd.po_header_id po_header_id , pd.po_line_id po_line_id , (select he.PERSON_ID from per_people_f he where pd.deliver_to_person_id = he.person_id AND TRUNC(sysdate) > TRUNC(he.effective_start_date) AND TRUNC(sysdate) < TRUNC(he.effective_end_date)) requestor , /* Modified for bug#22652320 / pd.distribution_type distribution_type , pll.MATCHING_BASIS MATCHING_BASIS , pll.match_option match_option , pll.price_override shipment_unit_price, DECODE(pll.matching_basis,'QUANTITY',pd.quantity_delivered,NVL(pd.amount_delivered,0)) delivered , DECODE(pll.matching_basis,'QUANTITY',pd.quantity_billed,NVL(pd.amount_billed,0)) billed , DECODE(pll.matching_basis,'QUANTITY',pd.quantity_ordered,NVL(pd.amount_ordered,0)) ordered , pd.quantity_ordered quantity_ordered , pd.quantity_billed quantity_billed , pd.quantity_cancelled quantity_cancelled , pd.quantity_delivered quantity_delivered , pd.quantity_financed quantity_financed , pd.quantity_recouped quantity_recouped , pd.amount_ordered amount_ordered , pd.amount_billed amount_billed , pd.amount_cancelled amount_cancelled , pd.amount_delivered amount_delivered , pd.amount_financed amount_financed , pd.amount_recouped amount_recouped , alc.displayed_field value_basis_distribution , NVL(pd.accrue_on_receipt_flag, 'N') accrue_on_receipt_flag , DECODE(pd.destination_type_code, 'EXPENSE', DECODE(pd.accrue_on_receipt_flag, 'Y', pd.accrual_account_id, pd.code_combination_id), pd.accrual_account_id) code_combination_id, pd.deliver_to_location_id deliver_to_location_id , pd.distribution_num distribution_num , pd.line_location_id line_location_id , asp.allow_flex_override_flag ALLOW_FLEX_OVERRIDE_FLAG , fsp.purch_encumbrance_flag PURCH_ENCUMBRANCE_FLAG , asp.transfer_desc_flex_flag TRANSFER_DESC_FLEX_FLAG , DECODE(pd.destination_type_code, 'EXPENSE' , pd.pjc_expenditure_type_id, NULL) pjc_expenditure_type_id , DECODE(pd.destination_type_code, 'EXPENSE' , pd.pjc_expenditure_item_date, NULL) pjc_expenditure_item_date , DECODE(pd.destination_type_code, 'EXPENSE' , pd.pjc_organization_id, NULL) pjc_organization_id , DECODE(pd.destination_type_code, 'EXPENSE' , pd.pjc_project_id, NULL) pjc_project_id , DECODE(pd.destination_type_code, 'EXPENSE' , pd.pjc_task_id, NULL) pjc_task_id , DECODE(PD.pjc_project_id,NULL, 'E', DECODE(pd.destination_type_code, 'INVENTORY','M','SHOP FLOOR','M','N')) pa_addition_flag , pd.variance_account_id , pd.attribute1 attribute1 , pd.attribute10 attribute10 , pd.attribute11 attribute11 , pd.attribute12 attribute12 , pd.attribute13 attribute13 , pd.attribute14 attribute14 , pd.attribute15 attribute15 , pd.attribute2 attribute2 , pd.attribute3 attribute3 , pd.attribute4 attribute4 , pd.attribute5 attribute5 , pd.attribute6 attribute6 , pd.attribute7 attribute7 , pd.attribute8 attribute8 , pd.attribute9 attribute9 , pd.attribute_category attribute_category , (select inv.unit_of_measure from inv_units_of_measure_vl inv where inv.uom_code = pll.uom_code ) PO_UOM , / Modified for bug#22652320 / 'PO_Purchase_Order_Schedule' pjc_context_category , pd.pjc_billable_flag pjc_billable_flag , pd.pjc_user_def_attribute1 pjc_user_def_attribute1 , pd.pjc_user_def_attribute2 pjc_user_def_attribute2 , pd.pjc_user_def_attribute3 pjc_user_def_attribute3 , pd.pjc_user_def_attribute4 pjc_user_def_attribute4 , pd.pjc_user_def_attribute5 pjc_user_def_attribute5 , pd.pjc_user_def_attribute6 pjc_user_def_attribute6 , pd.pjc_user_def_attribute7 pjc_user_def_attribute7 , pd.pjc_user_def_attribute8 pjc_user_def_attribute8 , pd.pjc_user_def_attribute9 pjc_user_def_attribute9 , pd.pjc_user_def_attribute10 pjc_user_def_attribute10 , pd.pjc_contract_id pjc_contract_id , pd.pjc_work_type_id pjc_work_type_id , pd.pjc_capitalizable_flag pjc_capitalizable_flag , pd.pjc_reserved_attribute1 pjc_reserved_attribute1 , pd.pjc_reserved_attribute2 pjc_reserved_attribute2 , pd.pjc_reserved_attribute3 pjc_reserved_attribute3 , pd.pjc_reserved_attribute4 pjc_reserved_attribute4 , pd.pjc_reserved_attribute5 pjc_reserved_attribute5 , pd.pjc_reserved_attribute6 pjc_reserved_attribute6 , pd.pjc_reserved_attribute7 pjc_reserved_attribute7 , pd.pjc_reserved_attribute8 pjc_reserved_attribute8 , pd.pjc_reserved_attribute9 pjc_reserved_attribute9 , pd.pjc_reserved_attribute10 pjc_reserved_attribute10 , pd.pjc_funding_allocation_id pjc_funding_allocation_id , pd.pjc_contract_line_id pjc_contract_line_id, ASP.build_prepayment_accounts_flag, NVL(psa.prepay_code_combination_id, fsp.prepay_code_combination_id) prepay_code_combination_id, NVL(pl.credit_flag, 'N') credit_flag FROM po_distributions_all pd , po_line_locations_all pll , / Commented out for bug#22652320 per_people_f he , hr_org_units_no_join hrou , hr_all_organization_units_tl hout, / ap_lookup_codes alc , ap_system_parameters_all asp , financials_system_params_all fsp, /bug 30655351/ po_headers_all ph, po_lines_all pl, POZ_SITE_ASSIGNMENTS_ALL_M PSA / inv_units_of_measure_vl inv Commented out for bug#22652320 / WHERE pd.LINE_LOCATION_ID = pll.LINE_LOCATION_ID(+) AND pll.po_line_id = pl.po_line_id / Commented out for bug#22652320 AND pd.deliver_to_person_id = he.person_id(+) AND TRUNC(sysdate) > TRUNC(he.effective_start_date(+)) AND TRUNC(sysdate) < TRUNC(he.effective_end_date(+)) AND pd.pjc_organization_id = hrou.organization_id(+) AND hrou.organization_id = hout.organization_id(+) AND inv.uom_code (+) = pll.uom_code AND DECODE(hout.organization_id, NULL, '1', hout.language) = DECODE(hout.organization_id, NULL, '1', USERENV('LANG')) */ AND alc.lookup_type = 'VALUE_BASIS' AND alc.lookup_code = DECODE(pll.matching_basis,'QUANTITY','DISTRIBUTION QUANTITY','DISTRIBUTION AMOUNT') AND fsp.org_id = ph.billto_bu_id AND asp.org_id = ph.billto_bu_id AND pd.po_header_id = ph.po_header_id AND ph.vendor_site_id = psa.vendor_site_id AND TRUNC(NVL (psa.inactive_date,sysdate+1)) > TRUNC(sysdate) AND PSA.BU_ID = ASP.ORG_ID AND PSA.BU_ID = FSP.ORG_ID

SQL_Statement

SELECT ph.billto_bu_id org_id ,

pd.po_distribution_id po_distribution_id ,

pd.po_header_id po_header_id ,

pd.po_line_id po_line_id ,

(select he.PERSON_ID from per_people_f he where pd.deliver_to_person_id = he.person_id AND TRUNC(sysdate) > TRUNC(he.effective_start_date) AND TRUNC(sysdate) < TRUNC(he.effective_end_date)) requestor , /* Modified for bug#22652320 */

pd.distribution_type distribution_type ,

pll.MATCHING_BASIS MATCHING_BASIS ,

pll.match_option match_option ,

pll.price_override shipment_unit_price,

DECODE(pll.matching_basis,'QUANTITY',pd.quantity_delivered,NVL(pd.amount_delivered,0)) delivered ,

DECODE(pll.matching_basis,'QUANTITY',pd.quantity_billed,NVL(pd.amount_billed,0)) billed ,

DECODE(pll.matching_basis,'QUANTITY',pd.quantity_ordered,NVL(pd.amount_ordered,0)) ordered ,

pd.quantity_ordered quantity_ordered ,

pd.quantity_billed quantity_billed ,

pd.quantity_cancelled quantity_cancelled ,

pd.quantity_delivered quantity_delivered ,

pd.quantity_financed quantity_financed ,

pd.quantity_recouped quantity_recouped ,

pd.amount_ordered amount_ordered ,

pd.amount_billed amount_billed ,

pd.amount_cancelled amount_cancelled ,

pd.amount_delivered amount_delivered ,

pd.amount_financed amount_financed ,

pd.amount_recouped amount_recouped ,

alc.displayed_field value_basis_distribution ,

NVL(pd.accrue_on_receipt_flag, 'N') accrue_on_receipt_flag ,

DECODE(pd.destination_type_code, 'EXPENSE', DECODE(pd.accrue_on_receipt_flag, 'Y', pd.accrual_account_id, pd.code_combination_id), pd.accrual_account_id) code_combination_id,

pd.deliver_to_location_id deliver_to_location_id ,

pd.distribution_num distribution_num ,

pd.line_location_id line_location_id ,

asp.allow_flex_override_flag ALLOW_FLEX_OVERRIDE_FLAG ,

fsp.purch_encumbrance_flag PURCH_ENCUMBRANCE_FLAG ,

asp.transfer_desc_flex_flag TRANSFER_DESC_FLEX_FLAG ,

DECODE(pd.destination_type_code, 'EXPENSE' , pd.pjc_expenditure_type_id, NULL) pjc_expenditure_type_id ,

DECODE(pd.destination_type_code, 'EXPENSE' , pd.pjc_expenditure_item_date, NULL) pjc_expenditure_item_date ,

DECODE(pd.destination_type_code, 'EXPENSE' , pd.pjc_organization_id, NULL) pjc_organization_id ,

DECODE(pd.destination_type_code, 'EXPENSE' , pd.pjc_project_id, NULL) pjc_project_id ,

DECODE(pd.destination_type_code, 'EXPENSE' , pd.pjc_task_id, NULL) pjc_task_id ,

DECODE(PD.pjc_project_id,NULL, 'E', DECODE(pd.destination_type_code, 'INVENTORY','M','SHOP FLOOR','M','N')) pa_addition_flag ,

pd.variance_account_id ,

pd.attribute1 attribute1 ,

pd.attribute10 attribute10 ,

pd.attribute11 attribute11 ,

pd.attribute12 attribute12 ,

pd.attribute13 attribute13 ,

pd.attribute14 attribute14 ,

pd.attribute15 attribute15 ,

pd.attribute2 attribute2 ,

pd.attribute3 attribute3 ,

pd.attribute4 attribute4 ,

pd.attribute5 attribute5 ,

pd.attribute6 attribute6 ,

pd.attribute7 attribute7 ,

pd.attribute8 attribute8 ,

pd.attribute9 attribute9 ,

pd.attribute_category attribute_category ,

(select inv.unit_of_measure from inv_units_of_measure_vl inv where inv.uom_code = pll.uom_code ) PO_UOM , /* Modified for bug#22652320 */

'PO_Purchase_Order_Schedule' pjc_context_category ,

pd.pjc_billable_flag pjc_billable_flag ,

pd.pjc_user_def_attribute1 pjc_user_def_attribute1 ,

pd.pjc_user_def_attribute2 pjc_user_def_attribute2 ,

pd.pjc_user_def_attribute3 pjc_user_def_attribute3 ,

pd.pjc_user_def_attribute4 pjc_user_def_attribute4 ,

pd.pjc_user_def_attribute5 pjc_user_def_attribute5 ,

pd.pjc_user_def_attribute6 pjc_user_def_attribute6 ,

pd.pjc_user_def_attribute7 pjc_user_def_attribute7 ,

pd.pjc_user_def_attribute8 pjc_user_def_attribute8 ,

pd.pjc_user_def_attribute9 pjc_user_def_attribute9 ,

pd.pjc_user_def_attribute10 pjc_user_def_attribute10 ,

pd.pjc_contract_id pjc_contract_id ,

pd.pjc_work_type_id pjc_work_type_id ,

pd.pjc_capitalizable_flag pjc_capitalizable_flag ,

pd.pjc_reserved_attribute1 pjc_reserved_attribute1 ,

pd.pjc_reserved_attribute2 pjc_reserved_attribute2 ,

pd.pjc_reserved_attribute3 pjc_reserved_attribute3 ,

pd.pjc_reserved_attribute4 pjc_reserved_attribute4 ,

pd.pjc_reserved_attribute5 pjc_reserved_attribute5 ,

pd.pjc_reserved_attribute6 pjc_reserved_attribute6 ,

pd.pjc_reserved_attribute7 pjc_reserved_attribute7 ,

pd.pjc_reserved_attribute8 pjc_reserved_attribute8 ,

pd.pjc_reserved_attribute9 pjc_reserved_attribute9 ,

pd.pjc_reserved_attribute10 pjc_reserved_attribute10 ,

pd.pjc_funding_allocation_id pjc_funding_allocation_id ,

pd.pjc_contract_line_id pjc_contract_line_id,

ASP.build_prepayment_accounts_flag,

NVL(psa.prepay_code_combination_id, fsp.prepay_code_combination_id) prepay_code_combination_id,

NVL(pl.credit_flag, 'N') credit_flag

FROM po_distributions_all pd ,

po_line_locations_all pll ,

/* Commented out for bug#22652320

per_people_f he ,

hr_org_units_no_join hrou ,

hr_all_organization_units_tl hout, */

ap_lookup_codes alc ,

ap_system_parameters_all asp ,

financials_system_params_all fsp, /*bug 30655351*/

po_headers_all ph,

po_lines_all pl,

POZ_SITE_ASSIGNMENTS_ALL_M PSA

/* inv_units_of_measure_vl inv Commented out for bug#22652320 */

WHERE pd.LINE_LOCATION_ID = pll.LINE_LOCATION_ID(+)

AND pll.po_line_id = pl.po_line_id

/* Commented out for bug#22652320

AND pd.deliver_to_person_id = he.person_id(+)

AND TRUNC(sysdate) > TRUNC(he.effective_start_date(+))

AND TRUNC(sysdate) < TRUNC(he.effective_end_date(+))

AND pd.pjc_organization_id = hrou.organization_id(+)

AND hrou.organization_id = hout.organization_id(+)

AND inv.uom_code (+) = pll.uom_code

AND DECODE(hout.organization_id, NULL, '1', hout.language) = DECODE(hout.organization_id, NULL, '1', USERENV('LANG')) */

AND alc.lookup_type = 'VALUE_BASIS'

AND alc.lookup_code = DECODE(pll.matching_basis,'QUANTITY','DISTRIBUTION QUANTITY','DISTRIBUTION AMOUNT')

AND fsp.org_id = ph.billto_bu_id

AND asp.org_id = ph.billto_bu_id

AND pd.po_header_id = ph.po_header_id

AND ph.vendor_site_id = psa.vendor_site_id

AND TRUNC(NVL (psa.inactive_date,sysdate+1)) > TRUNC(sysdate)

AND PSA.BU_ID = ASP.ORG_ID

AND PSA.BU_ID = FSP.ORG_ID