AP_DISTRIBUTION_MATCH_V
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 |
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 |