VIEW: APPS.POA_PQC_BS_V

Source

View Text - Preformatted

SELECT f.org_id org_id, f.ship_to_org_id ship_to_org_id, f.supplier_id supplier_id, f.supplier_site_id supplier_site_id, f.commodity_id commodity_id, f.category_id category_id, f.po_item_id po_item_id, f.base_uom base_uom, f.buyer_id buyer_id, f.approved_date approved_date, f.company_id company_id, f.cost_center_id cost_center_id, cip.purchase_amt_g/decode(cip.quantity,0,to_number(null),cip.quantity) * Nvl(f.quantity,0) pbcqco_amt_g, cip.purchase_amt_sg/decode(cip.quantity,0,to_number(null),cip.quantity) * Nvl(f.quantity,0) pbcqco_amt_sg, cip.purchase_amt_b/decode(cip.quantity,0,to_number(null),cip.quantity) * Nvl(f.quantity,0) pbcqco_amt_b, nvl(pip.purchase_amt_g/decode(pip.quantity,0,to_number(null),pip.quantity), cip.purchase_amt_g/decode(cip.quantity,0,to_number(null),cip.quantity)) * Nvl(f.quantity,0) pbpcqco_amt_g, nvl(pip.purchase_amt_sg/decode(pip.quantity,0,to_number(null),pip.quantity), cip.purchase_amt_sg/decode(cip.quantity,0,to_number(null),cip.quantity)) * Nvl(f.quantity,0) pbpcqco_amt_sg, nvl(pip.purchase_amt_b/decode(pip.quantity,0,to_number(null),pip.quantity), cip.purchase_amt_b/decode(cip.quantity,0,to_number(null),cip.quantity)) * Nvl(f.quantity,0) pbpcqco_amt_b, nvl(pisp.purchase_amt_g/decode(pisp.quantity,0,to_number(null),pisp.quantity), cisp.purchase_amt_g/decode(cisp.quantity,0,to_number(null),cisp.quantity)) * Nvl(f.quantity,0) pbpcqcs_amt_g, nvl(pisp.purchase_amt_sg/decode(pisp.quantity,0,to_number(null),pisp.quantity), cisp.purchase_amt_sg/decode(cisp.quantity,0,to_number(null),cisp.quantity)) * Nvl(f.quantity,0) pbpcqcs_amt_sg, nvl(pisp.purchase_amt_g/decode(pisp.quantity,0,to_number(null),pisp.quantity), cisp.purchase_amt_g/decode(cisp.quantity,0,to_number(null),cisp.quantity)) * Nvl(f.quantity,0) / decode(f.global_cur_conv_rate,0,1,f.global_cur_conv_rate) pbpcqcs_amt_b, nvl(pip.purchase_amt_g/decode(pip.quantity,0,to_number(null),pip.quantity), cip.purchase_amt_g/decode(cip.quantity,0,to_number(null),cip.quantity)) benchmarkprice_o_g, nvl(pip.purchase_amt_sg/decode(pip.quantity,0,to_number(null),pip.quantity), cip.purchase_amt_sg/decode(cip.quantity,0,to_number(null),cip.quantity)) benchmarkprice_o_sg, nvl(pip.purchase_amt_b/decode(pip.quantity,0,to_number(null),pip.quantity), cip.purchase_amt_b/decode(cip.quantity,0,to_number(null),cip.quantity)) benchmarkprice_o_b, nvl(pisp.purchase_amt_g/decode(pisp.quantity,0,to_number(null),pisp.quantity), cisp.purchase_amt_g/decode(cisp.quantity,0,to_number(null),cisp.quantity)) benchmarkprice_s_g, nvl(pisp.purchase_amt_sg/decode(pisp.quantity,0,to_number(null),pisp.quantity), cisp.purchase_amt_sg/decode(cisp.quantity,0,to_number(null),cisp.quantity)) benchmarkprice_s_sg, nvl(pisp.purchase_amt_g/decode(pisp.quantity,0,to_number(null),pisp.quantity), cisp.purchase_amt_g/decode(cisp.quantity,0,to_number(null),cisp.quantity)) / Decode(f.global_cur_conv_rate,0,1,f.global_cur_conv_rate) benchmarkprice_s_b, f.purchase_amt_b purchase_amt_b, f.purchase_amt_g purchase_amt_g, f.purchase_amt_sg purchase_amt_sg, Nvl(f.quantity,0) quantity, f.po_distribution_id po_distribution_id, f.line_location_id line_location_id from poa_pqc_bs_j_mv f, poa_bm_item_o_mv pip, poa_bm_item_o_mv cip, poa_bm_item_s_mv pisp, poa_bm_item_s_mv cisp WHERE f.p_ent_year_id = pip.ent_year_id(+) and f.org_id = pip.org_id(+) and f.po_item_id = pip.po_item_id(+) and f.base_uom = pip.base_uom(+) and f.p_ent_year_id = pisp.ent_year_id(+) and f.po_item_id = pisp.po_item_id(+) and f.base_uom = pisp.base_uom(+) and f.supplier_id = pisp.supplier_id(+) and f.ent_year_id = cip.ent_year_id and f.org_id = cip.org_id and f.po_item_id = cip.po_item_id and f.base_uom = cip.base_uom and f.ent_year_id = cisp.ent_year_id and f.po_item_id = cisp.po_item_id and f.base_uom = cisp.base_uom and f.supplier_id = cisp.supplier_id and f.complex_work_flag = 'N' and f.consigned_code <> 1 and f.order_type = 'QUANTITY'

View Text - HTML Formatted

SELECT F.ORG_ID ORG_ID
, F.SHIP_TO_ORG_ID SHIP_TO_ORG_ID
, F.SUPPLIER_ID SUPPLIER_ID
, F.SUPPLIER_SITE_ID SUPPLIER_SITE_ID
, F.COMMODITY_ID COMMODITY_ID
, F.CATEGORY_ID CATEGORY_ID
, F.PO_ITEM_ID PO_ITEM_ID
, F.BASE_UOM BASE_UOM
, F.BUYER_ID BUYER_ID
, F.APPROVED_DATE APPROVED_DATE
, F.COMPANY_ID COMPANY_ID
, F.COST_CENTER_ID COST_CENTER_ID
, CIP.PURCHASE_AMT_G/DECODE(CIP.QUANTITY
, 0
, TO_NUMBER(NULL)
, CIP.QUANTITY) * NVL(F.QUANTITY
, 0) PBCQCO_AMT_G
, CIP.PURCHASE_AMT_SG/DECODE(CIP.QUANTITY
, 0
, TO_NUMBER(NULL)
, CIP.QUANTITY) * NVL(F.QUANTITY
, 0) PBCQCO_AMT_SG
, CIP.PURCHASE_AMT_B/DECODE(CIP.QUANTITY
, 0
, TO_NUMBER(NULL)
, CIP.QUANTITY) * NVL(F.QUANTITY
, 0) PBCQCO_AMT_B
, NVL(PIP.PURCHASE_AMT_G/DECODE(PIP.QUANTITY
, 0
, TO_NUMBER(NULL)
, PIP.QUANTITY)
, CIP.PURCHASE_AMT_G/DECODE(CIP.QUANTITY
, 0
, TO_NUMBER(NULL)
, CIP.QUANTITY)) * NVL(F.QUANTITY
, 0) PBPCQCO_AMT_G
, NVL(PIP.PURCHASE_AMT_SG/DECODE(PIP.QUANTITY
, 0
, TO_NUMBER(NULL)
, PIP.QUANTITY)
, CIP.PURCHASE_AMT_SG/DECODE(CIP.QUANTITY
, 0
, TO_NUMBER(NULL)
, CIP.QUANTITY)) * NVL(F.QUANTITY
, 0) PBPCQCO_AMT_SG
, NVL(PIP.PURCHASE_AMT_B/DECODE(PIP.QUANTITY
, 0
, TO_NUMBER(NULL)
, PIP.QUANTITY)
, CIP.PURCHASE_AMT_B/DECODE(CIP.QUANTITY
, 0
, TO_NUMBER(NULL)
, CIP.QUANTITY)) * NVL(F.QUANTITY
, 0) PBPCQCO_AMT_B
, NVL(PISP.PURCHASE_AMT_G/DECODE(PISP.QUANTITY
, 0
, TO_NUMBER(NULL)
, PISP.QUANTITY)
, CISP.PURCHASE_AMT_G/DECODE(CISP.QUANTITY
, 0
, TO_NUMBER(NULL)
, CISP.QUANTITY)) * NVL(F.QUANTITY
, 0) PBPCQCS_AMT_G
, NVL(PISP.PURCHASE_AMT_SG/DECODE(PISP.QUANTITY
, 0
, TO_NUMBER(NULL)
, PISP.QUANTITY)
, CISP.PURCHASE_AMT_SG/DECODE(CISP.QUANTITY
, 0
, TO_NUMBER(NULL)
, CISP.QUANTITY)) * NVL(F.QUANTITY
, 0) PBPCQCS_AMT_SG
, NVL(PISP.PURCHASE_AMT_G/DECODE(PISP.QUANTITY
, 0
, TO_NUMBER(NULL)
, PISP.QUANTITY)
, CISP.PURCHASE_AMT_G/DECODE(CISP.QUANTITY
, 0
, TO_NUMBER(NULL)
, CISP.QUANTITY)) * NVL(F.QUANTITY
, 0) / DECODE(F.GLOBAL_CUR_CONV_RATE
, 0
, 1
, F.GLOBAL_CUR_CONV_RATE) PBPCQCS_AMT_B
, NVL(PIP.PURCHASE_AMT_G/DECODE(PIP.QUANTITY
, 0
, TO_NUMBER(NULL)
, PIP.QUANTITY)
, CIP.PURCHASE_AMT_G/DECODE(CIP.QUANTITY
, 0
, TO_NUMBER(NULL)
, CIP.QUANTITY)) BENCHMARKPRICE_O_G
, NVL(PIP.PURCHASE_AMT_SG/DECODE(PIP.QUANTITY
, 0
, TO_NUMBER(NULL)
, PIP.QUANTITY)
, CIP.PURCHASE_AMT_SG/DECODE(CIP.QUANTITY
, 0
, TO_NUMBER(NULL)
, CIP.QUANTITY)) BENCHMARKPRICE_O_SG
, NVL(PIP.PURCHASE_AMT_B/DECODE(PIP.QUANTITY
, 0
, TO_NUMBER(NULL)
, PIP.QUANTITY)
, CIP.PURCHASE_AMT_B/DECODE(CIP.QUANTITY
, 0
, TO_NUMBER(NULL)
, CIP.QUANTITY)) BENCHMARKPRICE_O_B
, NVL(PISP.PURCHASE_AMT_G/DECODE(PISP.QUANTITY
, 0
, TO_NUMBER(NULL)
, PISP.QUANTITY)
, CISP.PURCHASE_AMT_G/DECODE(CISP.QUANTITY
, 0
, TO_NUMBER(NULL)
, CISP.QUANTITY)) BENCHMARKPRICE_S_G
, NVL(PISP.PURCHASE_AMT_SG/DECODE(PISP.QUANTITY
, 0
, TO_NUMBER(NULL)
, PISP.QUANTITY)
, CISP.PURCHASE_AMT_SG/DECODE(CISP.QUANTITY
, 0
, TO_NUMBER(NULL)
, CISP.QUANTITY)) BENCHMARKPRICE_S_SG
, NVL(PISP.PURCHASE_AMT_G/DECODE(PISP.QUANTITY
, 0
, TO_NUMBER(NULL)
, PISP.QUANTITY)
, CISP.PURCHASE_AMT_G/DECODE(CISP.QUANTITY
, 0
, TO_NUMBER(NULL)
, CISP.QUANTITY)) / DECODE(F.GLOBAL_CUR_CONV_RATE
, 0
, 1
, F.GLOBAL_CUR_CONV_RATE) BENCHMARKPRICE_S_B
, F.PURCHASE_AMT_B PURCHASE_AMT_B
, F.PURCHASE_AMT_G PURCHASE_AMT_G
, F.PURCHASE_AMT_SG PURCHASE_AMT_SG
, NVL(F.QUANTITY
, 0) QUANTITY
, F.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID
, F.LINE_LOCATION_ID LINE_LOCATION_ID
FROM POA_PQC_BS_J_MV F
, POA_BM_ITEM_O_MV PIP
, POA_BM_ITEM_O_MV CIP
, POA_BM_ITEM_S_MV PISP
, POA_BM_ITEM_S_MV CISP
WHERE F.P_ENT_YEAR_ID = PIP.ENT_YEAR_ID(+)
AND F.ORG_ID = PIP.ORG_ID(+)
AND F.PO_ITEM_ID = PIP.PO_ITEM_ID(+)
AND F.BASE_UOM = PIP.BASE_UOM(+)
AND F.P_ENT_YEAR_ID = PISP.ENT_YEAR_ID(+)
AND F.PO_ITEM_ID = PISP.PO_ITEM_ID(+)
AND F.BASE_UOM = PISP.BASE_UOM(+)
AND F.SUPPLIER_ID = PISP.SUPPLIER_ID(+)
AND F.ENT_YEAR_ID = CIP.ENT_YEAR_ID
AND F.ORG_ID = CIP.ORG_ID
AND F.PO_ITEM_ID = CIP.PO_ITEM_ID
AND F.BASE_UOM = CIP.BASE_UOM
AND F.ENT_YEAR_ID = CISP.ENT_YEAR_ID
AND F.PO_ITEM_ID = CISP.PO_ITEM_ID
AND F.BASE_UOM = CISP.BASE_UOM
AND F.SUPPLIER_ID = CISP.SUPPLIER_ID
AND F.COMPLEX_WORK_FLAG = 'N'
AND F.CONSIGNED_CODE <> 1
AND F.ORDER_TYPE = 'QUANTITY'