VIEW: APPS.POA_DBI_POD_F_V

Source

View Text - Preformatted

SELECT f.po_header_id, f.po_release_id, f.shipment_type, f.from_document_id, f.potential_contract_id, f.consigned_code, f.approved_date, f.creation_operating_unit_id org_id, nvl(com.commodity_id,-1) commodity_id, f.base_uom base_uom, f.supplier_id, f.supplier_site_id, f.category_id, f.po_item_id, f.buyer_id, map.parent_company_dim_id company_id, map.parent_cost_center_dim_id cost_center_id, (case when f.negotiated_by_preparer_flag = 'Y' then (case when f.shipment_type = 'BLANKET' THEN 1 when f.shipment_type = 'SCHEDULED' THEN 2 when f.from_document_type = 'BLANKET' then 3 when f.contract_amount_b > 0 then 4 end) else null end) contract_type, f.quantity quantity, f.purchase_amount_b * f.func_cur_conv_rate purchase_amt_b , decode( f.global_cur_conv_rate, 0, f.purchase_amount_b, f.purchase_amount_b * f.func_cur_conv_rate * f.global_cur_conv_rate ) purchase_amt_g, decode( f.sglobal_cur_conv_rate, 0, f.purchase_amount_b, f.purchase_amount_b * f.func_cur_conv_rate * f.sglobal_cur_conv_rate ) purchase_amt_sg, f.pot_contract_amount_b * f.func_cur_conv_rate p_contract_amt_b, decode( f.global_cur_conv_rate, 0, f.pot_contract_amount_b, f.pot_contract_amount_b * f.func_cur_conv_rate * f.global_cur_conv_rate ) p_contract_amt_g, decode( f.sglobal_cur_conv_rate, 0, f.pot_contract_amount_b, f.pot_contract_amount_b * f.func_cur_conv_rate * f.sglobal_cur_conv_rate ) p_contract_amt_sg, f.non_contract_amount_b * f.func_cur_conv_rate n_contract_amt_b, decode( f.global_cur_conv_rate, 0, f.non_contract_amount_b, f.non_contract_amount_b * f.func_cur_conv_rate * f.global_cur_conv_rate ) n_contract_amt_g, decode( f.sglobal_cur_conv_rate, 0, f.non_contract_amount_b, f.non_contract_amount_b * f.func_cur_conv_rate * f.sglobal_cur_conv_rate ) n_contract_amt_sg, f.pot_savings_amount_b * f.func_cur_conv_rate p_savings_amt_b, decode( f.global_cur_conv_rate, 0, f.pot_savings_amount_b, f.pot_savings_amount_b * f.func_cur_conv_rate * f.global_cur_conv_rate ) p_savings_amt_g, decode( f.sglobal_cur_conv_rate, 0, f.pot_savings_amount_b, f.pot_savings_amount_b * f.func_cur_conv_rate * f.sglobal_cur_conv_rate ) p_savings_amt_sg, f.contract_amount_b * f.func_cur_conv_rate contract_amt_b, decode( f.global_cur_conv_rate, 0, f.contract_amount_b, f.contract_amount_b * f.func_cur_conv_rate * f.global_cur_conv_rate ) contract_amt_g, decode( f.sglobal_cur_conv_rate, 0, f.contract_amount_b, f.contract_amount_b * f.func_cur_conv_rate * f.sglobal_cur_conv_rate ) contract_amt_sg, f.negotiated_by_preparer_flag from poa_dbi_pod_f f, po_commodity_categories com, fii_com_cc_dim_maps map WHERE f.category_id = com.category_id(+) and f.company_id = map.child_company_id(+) and f.cost_center_id = map.child_cost_center_id(+) and f.shipment_type <> 'PREPAYMENT'

View Text - HTML Formatted

SELECT F.PO_HEADER_ID
, F.PO_RELEASE_ID
, F.SHIPMENT_TYPE
, F.FROM_DOCUMENT_ID
, F.POTENTIAL_CONTRACT_ID
, F.CONSIGNED_CODE
, F.APPROVED_DATE
, F.CREATION_OPERATING_UNIT_ID ORG_ID
, NVL(COM.COMMODITY_ID
, -1) COMMODITY_ID
, F.BASE_UOM BASE_UOM
, F.SUPPLIER_ID
, F.SUPPLIER_SITE_ID
, F.CATEGORY_ID
, F.PO_ITEM_ID
, F.BUYER_ID
, MAP.PARENT_COMPANY_DIM_ID COMPANY_ID
, MAP.PARENT_COST_CENTER_DIM_ID COST_CENTER_ID
, (CASE WHEN F.NEGOTIATED_BY_PREPARER_FLAG = 'Y' THEN (CASE WHEN F.SHIPMENT_TYPE = 'BLANKET' THEN 1 WHEN F.SHIPMENT_TYPE = 'SCHEDULED' THEN 2 WHEN F.FROM_DOCUMENT_TYPE = 'BLANKET' THEN 3 WHEN F.CONTRACT_AMOUNT_B > 0 THEN 4 END) ELSE NULL END) CONTRACT_TYPE
, F.QUANTITY QUANTITY
, F.PURCHASE_AMOUNT_B * F.FUNC_CUR_CONV_RATE PURCHASE_AMT_B
, DECODE( F.GLOBAL_CUR_CONV_RATE
, 0
, F.PURCHASE_AMOUNT_B
, F.PURCHASE_AMOUNT_B * F.FUNC_CUR_CONV_RATE * F.GLOBAL_CUR_CONV_RATE ) PURCHASE_AMT_G
, DECODE( F.SGLOBAL_CUR_CONV_RATE
, 0
, F.PURCHASE_AMOUNT_B
, F.PURCHASE_AMOUNT_B * F.FUNC_CUR_CONV_RATE * F.SGLOBAL_CUR_CONV_RATE ) PURCHASE_AMT_SG
, F.POT_CONTRACT_AMOUNT_B * F.FUNC_CUR_CONV_RATE P_CONTRACT_AMT_B
, DECODE( F.GLOBAL_CUR_CONV_RATE
, 0
, F.POT_CONTRACT_AMOUNT_B
, F.POT_CONTRACT_AMOUNT_B * F.FUNC_CUR_CONV_RATE * F.GLOBAL_CUR_CONV_RATE ) P_CONTRACT_AMT_G
, DECODE( F.SGLOBAL_CUR_CONV_RATE
, 0
, F.POT_CONTRACT_AMOUNT_B
, F.POT_CONTRACT_AMOUNT_B * F.FUNC_CUR_CONV_RATE * F.SGLOBAL_CUR_CONV_RATE ) P_CONTRACT_AMT_SG
, F.NON_CONTRACT_AMOUNT_B * F.FUNC_CUR_CONV_RATE N_CONTRACT_AMT_B
, DECODE( F.GLOBAL_CUR_CONV_RATE
, 0
, F.NON_CONTRACT_AMOUNT_B
, F.NON_CONTRACT_AMOUNT_B * F.FUNC_CUR_CONV_RATE * F.GLOBAL_CUR_CONV_RATE ) N_CONTRACT_AMT_G
, DECODE( F.SGLOBAL_CUR_CONV_RATE
, 0
, F.NON_CONTRACT_AMOUNT_B
, F.NON_CONTRACT_AMOUNT_B * F.FUNC_CUR_CONV_RATE * F.SGLOBAL_CUR_CONV_RATE ) N_CONTRACT_AMT_SG
, F.POT_SAVINGS_AMOUNT_B * F.FUNC_CUR_CONV_RATE P_SAVINGS_AMT_B
, DECODE( F.GLOBAL_CUR_CONV_RATE
, 0
, F.POT_SAVINGS_AMOUNT_B
, F.POT_SAVINGS_AMOUNT_B * F.FUNC_CUR_CONV_RATE * F.GLOBAL_CUR_CONV_RATE ) P_SAVINGS_AMT_G
, DECODE( F.SGLOBAL_CUR_CONV_RATE
, 0
, F.POT_SAVINGS_AMOUNT_B
, F.POT_SAVINGS_AMOUNT_B * F.FUNC_CUR_CONV_RATE * F.SGLOBAL_CUR_CONV_RATE ) P_SAVINGS_AMT_SG
, F.CONTRACT_AMOUNT_B * F.FUNC_CUR_CONV_RATE CONTRACT_AMT_B
, DECODE( F.GLOBAL_CUR_CONV_RATE
, 0
, F.CONTRACT_AMOUNT_B
, F.CONTRACT_AMOUNT_B * F.FUNC_CUR_CONV_RATE * F.GLOBAL_CUR_CONV_RATE ) CONTRACT_AMT_G
, DECODE( F.SGLOBAL_CUR_CONV_RATE
, 0
, F.CONTRACT_AMOUNT_B
, F.CONTRACT_AMOUNT_B * F.FUNC_CUR_CONV_RATE * F.SGLOBAL_CUR_CONV_RATE ) CONTRACT_AMT_SG
, F.NEGOTIATED_BY_PREPARER_FLAG
FROM POA_DBI_POD_F F
, PO_COMMODITY_CATEGORIES COM
, FII_COM_CC_DIM_MAPS MAP
WHERE F.CATEGORY_ID = COM.CATEGORY_ID(+)
AND F.COMPANY_ID = MAP.CHILD_COMPANY_ID(+)
AND F.COST_CENTER_ID = MAP.CHILD_COST_CENTER_ID(+)
AND F.SHIPMENT_TYPE <> 'PREPAYMENT'