[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT o.transaction_type_id
,otl.name
,o.order_category_code
,o.warehouse_id
,o.agreement_required_flag
,o.po_required_flag
,o.entry_credit_check_rule_id
,o.start_date_active
,o.end_date_active
,i.rule_id
,a.rule_id
,pl.list_header_id
,sp.lookup_code
,sm.lookup_code
,fp.lookup_code
,ft.lookup_code
,dc.lookup_code
,lt.transaction_type_id
,o.conversion_type_code
,o.tax_calculation_event_code
,o.auto_scheduling_flag
,o.scheduling_level_code
,'Y'
,rl.QUICK_CR_CHECK_FLAG
,rtrx.tax_calculation_flag
,o.cust_trx_type_id
INTO G_ORDER_TYPE_TBL(p_key).order_type_id
,G_ORDER_TYPE_TBL(p_key).name
,G_ORDER_TYPE_TBL(p_key).order_category_code
,G_ORDER_TYPE_TBL(p_key).ship_from_org_id
,G_ORDER_TYPE_TBL(p_key).agreement_required_flag
,G_ORDER_TYPE_TBL(p_key).require_po_flag
,G_ORDER_TYPE_TBL(p_key).entry_credit_check_rule_id
,G_ORDER_TYPE_TBL(p_key).start_date_active
,G_ORDER_TYPE_TBL(p_key).end_date_active
,G_ORDER_TYPE_TBL(p_key).invoicing_rule_id
,G_ORDER_TYPE_TBL(p_key).accounting_rule_id
,G_ORDER_TYPE_TBL(p_key).price_list_id
,G_ORDER_TYPE_TBL(p_key).shipment_priority_code
,G_ORDER_TYPE_TBL(p_key).shipping_method_code
,G_ORDER_TYPE_TBL(p_key).fob_point_code
,G_ORDER_TYPE_TBL(p_key).freight_terms_code
,G_ORDER_TYPE_TBL(p_key).demand_class_code
,G_ORDER_TYPE_TBL(p_key).default_outbound_line_type_id
,G_ORDER_TYPE_TBL(p_key).conversion_type_code
,G_ORDER_TYPE_TBL(p_key).tax_calculation_event
,G_ORDER_TYPE_TBL(p_key).auto_scheduling_flag
,G_ORDER_TYPE_TBL(p_key).scheduling_level_code
,G_ORDER_TYPE_TBL(p_key).default_attributes
,G_ORDER_TYPE_TBL(p_key).quick_cr_check_flag
,G_ORDER_TYPE_TBL(p_key).tax_calculation_flag
,G_ORDER_TYPE_TBL(p_key).cust_trx_type_id
FROM oe_transaction_types_all o
,oe_transaction_types_tl otl
,oe_ra_rules_v i
,oe_ra_rules_v a
,qp_list_headers_vl pl
,oe_lookups sp
,oe_ship_methods_v sm
,oe_ar_lookups_v fp
,oe_lookups ft
,oe_fnd_common_lookups_v dc
,oe_transaction_types_all lt
,oe_credit_check_rules rl
,ra_cust_trx_types rtrx
WHERE o.transaction_type_id = p_key
AND o.invoicing_rule_id = i.rule_id(+)
AND i.status(+) = 'A'
AND i.type(+) = 'I'
AND o.accounting_rule_id = a.rule_id(+)
AND a.status(+) = 'A'
AND a.type(+) = 'A'
AND o.price_list_id = pl.list_header_id(+)
AND nvl(pl.active_flag(+),'Y') = 'Y'
AND o.shipment_priority_code = sp.lookup_code(+)
AND sp.lookup_type(+) = 'SHIPMENT_PRIORITY'
AND sp.enabled_flag(+) = 'Y'
AND sysdate between nvl(sp.start_date_active(+),sysdate)
and nvl(sp.end_date_active(+),sysdate)
AND o.shipping_method_code = sm.lookup_code(+)
AND sm.lookup_type(+) = 'SHIP_METHOD'
AND sm.enabled_flag(+) = 'Y'
AND sysdate between nvl(sm.start_date_active(+),sysdate)
and nvl(sm.end_date_active(+),sysdate)
AND o.fob_point_code = fp.lookup_code(+)
AND fp.lookup_type(+) = 'FOB'
AND fp.enabled_flag(+) = 'Y'
AND sysdate between nvl(fp.start_date_active(+),sysdate)
and nvl(fp.end_date_active(+),sysdate)
AND o.freight_terms_code = ft.lookup_code(+)
AND ft.lookup_type(+) = 'FREIGHT_TERMS'
AND ft.enabled_flag(+) = 'Y'
AND sysdate between nvl(ft.start_date_active(+),sysdate)
and nvl(ft.end_date_active(+),sysdate)
AND o.demand_class_code = dc.lookup_code(+)
AND dc.lookup_type(+) = 'DEMAND_CLASS'
AND dc.enabled_flag(+) = 'Y'
AND sysdate between nvl(dc.start_date_active(+),sysdate)
and nvl(dc.end_date_active(+),sysdate)
AND lt.transaction_type_id(+) = o.default_outbound_line_type_id
AND sysdate between nvl(lt.start_date_active(+),sysdate)
and nvl(lt.end_date_active(+),sysdate)
AND otl.transaction_type_id = o.transaction_type_id
AND otl.language = userenv('LANG')
AND o.entry_credit_check_rule_id = rl.credit_check_rule_id(+)
AND o.cust_trx_type_id = rtrx.cust_trx_type_id(+)
AND sysdate between nvl(rl.start_date_active(+),sysdate)
and nvl(rl.end_date_active(+),sysdate);
SELECT o.transaction_type_id
,otl.name
,o.order_category_code
,o.warehouse_id
,o.agreement_required_flag
,o.po_required_flag
,o.entry_credit_check_rule_id
,o.start_date_active
,o.end_date_active
,o.tax_calculation_event_code
,o.auto_scheduling_flag
,o.scheduling_level_code
,rl.quick_cr_check_flag
,rtrx.tax_calculation_flag
,o.cust_trx_type_id
INTO G_ORDER_TYPE_TBL(p_key).order_type_id
,G_ORDER_TYPE_TBL(p_key).name
,G_ORDER_TYPE_TBL(p_key).order_category_code
,G_ORDER_TYPE_TBL(p_key).ship_from_org_id
,G_ORDER_TYPE_TBL(p_key).agreement_required_flag
,G_ORDER_TYPE_TBL(p_key).require_po_flag
,G_ORDER_TYPE_TBL(p_key).entry_credit_check_rule_id
,G_ORDER_TYPE_TBL(p_key).start_date_active
,G_ORDER_TYPE_TBL(p_key).end_date_active
,G_ORDER_TYPE_TBL(p_key).tax_calculation_event
,G_ORDER_TYPE_TBL(p_key).auto_scheduling_flag
,G_ORDER_TYPE_TBL(p_key).scheduling_level_code
,G_ORDER_TYPE_TBL(p_key).quick_cr_check_flag
,G_ORDER_TYPE_TBL(p_key).tax_calculation_flag
,G_ORDER_TYPE_TBL(p_key).cust_trx_type_id
FROM OE_TRANSACTION_TYPES_ALL o
,oe_transaction_types_tl otl
,oe_credit_check_rules rl
,ra_cust_trx_types rtrx
WHERE o.transaction_type_id = p_key
AND otl.transaction_type_id = o.transaction_type_id
AND otl.language = userenv('LANG')
AND o.entry_credit_check_rule_id = rl.credit_check_rule_id(+)
AND o.cust_trx_type_id = rtrx.cust_trx_type_id(+)
AND sysdate between nvl(rl.start_date_active(+),sysdate)
and nvl(rl.end_date_active(+),sysdate);
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_ORDER_TYPE_TBL.DELETE(p_key);
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_ORDER_TYPE_TBL.DELETE(p_key);
SELECT /*+ PUSH_PRED(ct) */ o.transaction_type_id
,o.order_category_code
,o.start_date_active
,o.end_date_active
,o.cust_trx_type_id
,ct.tax_calculation_flag
,o.scheduling_level_code
INTO G_LINE_TYPE_TBL(p_key).line_type_id
,G_LINE_TYPE_TBL(p_key).order_category_code
,G_LINE_TYPE_TBL(p_key).start_date_active
,G_LINE_TYPE_TBL(p_key).end_date_active
,G_LINE_TYPE_TBL(p_key).cust_trx_type_id
,G_LINE_TYPE_TBL(p_key).tax_calculation_flag
,G_LINE_TYPE_TBL(p_key).scheduling_level_code
FROM oe_transaction_types_all o
,ra_cust_trx_types ct
WHERE o.transaction_type_id = p_key
AND o.cust_trx_type_id = ct.cust_trx_type_id(+);
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_LINE_TYPE_TBL.DELETE(p_key);
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_LINE_TYPE_TBL.DELETE(p_key);
SELECT a.agreement_id
,a.name
,a.start_date_active
,a.end_date_active
,a.revision
,a.sold_to_org_id
,a.price_list_id
,i.rule_id
,ac.rule_id
,term.term_id
,s.salesrep_id
,a.purchase_order_num
,a.invoice_contact_id
,a.invoice_to_org_id
,'Y'
INTO G_AGREEMENT_TBL(p_key).agreement_id
,G_AGREEMENT_TBL(p_key).name
,G_AGREEMENT_TBL(p_key).start_date_active
,G_AGREEMENT_TBL(p_key).end_date_active
,G_AGREEMENT_TBL(p_key).revision
,G_AGREEMENT_TBL(p_key).sold_to_org_id
,G_AGREEMENT_TBL(p_key).price_list_id
,G_AGREEMENT_TBL(p_key).invoicing_rule_id
,G_AGREEMENT_TBL(p_key).accounting_rule_id
,G_AGREEMENT_TBL(p_key).payment_term_id
,G_AGREEMENT_TBL(p_key).salesrep_id
,G_AGREEMENT_TBL(p_key).cust_po_number
,G_AGREEMENT_TBL(p_key).invoice_to_contact_id
,G_AGREEMENT_TBL(p_key).invoice_to_org_id
,G_AGREEMENT_TBL(p_key).default_attributes
FROM oe_agreements_vl a
,oe_ra_rules_v i
,oe_ra_rules_v ac
,oe_ra_terms_v term
,ra_salesreps s
WHERE a.agreement_id = p_key
AND a.invoicing_rule_id = i.rule_id(+)
AND i.status(+) = 'A'
AND i.type(+) = 'I'
AND a.accounting_rule_id = ac.rule_id(+)
AND ac.status(+) = 'A'
AND ac.type(+) = 'A'
AND a.term_id = term.term_id(+)
AND sysdate between nvl(term.start_date_active(+),sysdate)
and nvl(term.end_date_active(+),sysdate)
AND a.salesrep_id = s.salesrep_id(+)
AND sysdate between nvl(s.start_date_active(+),sysdate)
and nvl(s.end_date_active(+),sysdate)
;
SELECT a.agreement_id
,a.name
,a.start_date_active
,a.end_date_active
,a.revision
,a.sold_to_org_id
,a.price_list_id
INTO G_AGREEMENT_TBL(p_key).agreement_id
,G_AGREEMENT_TBL(p_key).name
,G_AGREEMENT_TBL(p_key).start_date_active
,G_AGREEMENT_TBL(p_key).end_date_active
,G_AGREEMENT_TBL(p_key).revision
,G_AGREEMENT_TBL(p_key).sold_to_org_id
,G_AGREEMENT_TBL(p_key).price_list_id
FROM oe_agreements_vl a
WHERE a.agreement_id = p_key;
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_AGREEMENT_TBL.DELETE(p_key);
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_AGREEMENT_TBL.DELETE(p_key);
SELECT dualum_ind
, item_id
, item_um
, item_um2
, grade_ctl -- OPM HVOP
FROM ic_item_mst
WHERE delete_mark = 0
AND item_no in (SELECT segment1
FROM mtl_system_items
WHERE organization_id = discrete_org_id
AND inventory_item_id = discrete_item_id);
SELECT msi.INVENTORY_ITEM_ID
,msi.ORGANIZATION_ID
,msi.CUSTOMER_ORDER_ENABLED_FLAG
,msi.INTERNAL_ORDER_ENABLED_FLAG
,msi.INVOICING_RULE_ID
,msi.ACCOUNTING_RULE_ID
,msi.DEFAULT_SHIPPING_ORG
,msi.SHIP_MODEL_COMPLETE_FLAG
,msi.BUILD_IN_WIP_FLAG
,msi.BOM_ITEM_TYPE
,msi.REPLENISH_TO_ORDER_FLAG
,msi.PRIMARY_UOM_CODE
,msi.PICK_COMPONENTS_FLAG
,msi.SHIPPABLE_ITEM_FLAG
,msi.SERVICE_ITEM_FLAG
,msi.OVER_SHIPMENT_TOLERANCE
,msi.UNDER_SHIPMENT_TOLERANCE
,msi.description
,msi.hazard_class_id
,msi.weight_uom_code
,msi.volume_uom_code
,msi.unit_volume
,msi.unit_weight
,DECODE(msi.mtl_transactions_enabled_flag, 'Y', 'Y', 'N')
pickable_flag
--bug 3798477
--,DECODE(msi.ONT_PRICING_QTY_SOURCE, 'P', 0, 'S',1,NULL) -- INVCONV
,msi.ONT_PRICING_QTY_SOURCE -- INVCONV
,msi.TRACKING_QUANTITY_IND
--bug 3798477
,msi.SECONDARY_UOM_CODE
-- INVCONV start
,msi.SECONDARY_DEFAULT_IND
,msi.LOT_DIVISIBLE_FLAG
,msi.GRADE_CONTROL_FLAG
,msi.LOT_CONTROL_CODE
,msi.CONFIG_MODEL_TYPE -- added for supporting configurations
,msi.PLANNING_MAKE_BUY_CODE
,kfv.concatenated_segments
,msi.full_lead_time
,msi.fixed_lead_time
,msi.variable_lead_time
INTO G_ITEM_TBL(p_key1).inventory_item_id
,G_ITEM_TBL(p_key1).organization_id
,G_ITEM_TBL(p_key1).customer_order_enabled_flag
,G_ITEM_TBL(p_key1).internal_order_enabled_flag
,G_ITEM_TBL(p_key1).invoicing_rule_id
,G_ITEM_TBL(p_key1).accounting_rule_id
,G_ITEM_TBL(p_key1).default_shipping_org
,G_ITEM_TBL(p_key1).ship_model_complete_flag
,G_ITEM_TBL(p_key1).build_in_wip_flag
,G_ITEM_TBL(p_key1).bom_item_type
,G_ITEM_TBL(p_key1).replenish_to_order_flag
,G_ITEM_TBL(p_key1).primary_uom_code
,G_ITEM_TBL(p_key1).pick_components_flag
,G_ITEM_TBL(p_key1).shippable_item_flag
,G_ITEM_TBL(p_key1).service_item_flag
,G_ITEM_TBL(p_key1).ship_tolerance_above
,G_ITEM_TBL(p_key1).ship_tolerance_below
,G_ITEM_TBL(p_key1).item_description
,G_ITEM_TBL(p_key1).hazard_class_id
,G_ITEM_TBL(p_key1).weight_uom_code
,G_ITEM_TBL(p_key1).volume_uom_code
,G_ITEM_TBL(p_key1).unit_volume
,G_ITEM_TBL(p_key1).unit_weight
,G_ITEM_TBL(p_key1).pickable_flag
--bug 3798477
,G_ITEM_TBL(p_key1).ont_pricing_qty_source
,G_ITEM_TBL(p_key1).tracking_quantity_ind
--bug 3798477
-- INCONV
,G_ITEM_TBL(p_key1).secondary_uom_code
,G_ITEM_TBL(p_key1).secondary_default_ind
,G_ITEM_TBL(p_key1).lot_divisible_flag
,G_ITEM_TBL(p_key1).grade_control_flag
,G_ITEM_TBL(p_key1).lot_control_code
,G_ITEM_TBL(p_key1).config_model_type --- added for supporting configurations
,G_ITEM_TBL(p_key1).planning_make_buy_code
,G_ITEM_TBL(p_key1).ordered_item
,G_ITEM_TBL(p_key1).full_lead_time
,G_ITEM_TBL(p_key1).fixed_lead_time
,G_ITEM_TBL(p_key1).variable_lead_time
FROM MTL_SYSTEM_ITEMS msi,
MTL_SYSTEM_ITEMS_KFV kfv
WHERE msi.INVENTORY_ITEM_ID = p_key1
AND msi.ORGANIZATION_ID = l_key2
AND kfv.INVENTORY_ITEM_ID = p_key1
AND kfv.ORGANIZATION_ID = l_key2;
SELECT shippable_item_flag
,organization_id
,primary_uom_code
,description
,hazard_class_id
,weight_uom_code
,volume_uom_code
,unit_volume
,unit_weight
,DECODE(mtl_transactions_enabled_flag, 'Y', 'Y', 'N')
pickable_flag
-- INVCONV start
,ONT_PRICING_QTY_SOURCE
,TRACKING_QUANTITY_IND
,SECONDARY_UOM_CODE
,SECONDARY_DEFAULT_IND
,LOT_DIVISIBLE_FLAG
,GRADE_CONTROL_FLAG
,LOT_CONTROL_CODE
INTO G_ITEM_TBL(p_key1).shippable_item_flag
,G_ITEM_TBL(p_key1).organization_id
,G_ITEM_TBL(p_key1).primary_uom_code
,G_ITEM_TBL(p_key1).item_description
,G_ITEM_TBL(p_key1).hazard_class_id
,G_ITEM_TBL(p_key1).weight_uom_code
,G_ITEM_TBL(p_key1).volume_uom_code
,G_ITEM_TBL(p_key1).unit_volume
,G_ITEM_TBL(p_key1).unit_weight
,G_ITEM_TBL(p_key1).pickable_flag
-- INVCONV start
,G_ITEM_TBL(p_key1).ont_pricing_qty_source
,G_ITEM_TBL(p_key1).tracking_quantity_ind
,G_ITEM_TBL(p_key1).secondary_uom_code
,G_ITEM_TBL(p_key1).secondary_default_ind
,G_ITEM_TBL(p_key1).lot_divisible_flag
,G_ITEM_TBL(p_key1).grade_control_flag
,G_ITEM_TBL(p_key1).lot_control_code
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = p_key1
AND ORGANIZATION_ID = p_key2; -- ship from org
SELECT wms_enabled_flag
INTO G_ITEM_TBL(p_key1).wms_enabled_flag
FROM mtl_parameters
WHERE organization_id = p_key2;
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_ITEM_TBL.DELETE(p_key1);
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_ITEM_TBL.DELETE(p_key1);
SELECT /* MOAC_SQL_CHANGE */ s.site_use_id
,a.cust_account_id
INTO G_SHIP_TO_TBL(p_key).ship_to_org_id
,G_SHIP_TO_TBL(p_key).customer_id
FROM hz_cust_site_uses_all s
,hz_cust_acct_sites a
WHERE s.site_use_id = p_key
AND s.site_use_code = 'SHIP_TO'
AND s.cust_acct_site_id = a.cust_acct_site_id
AND s.status = 'A'
AND a.status ='A'; --bug 2752321
SELECT /* MOAC_SQL_CHANGE */ s.site_use_id
,a.cust_account_id
,s.warehouse_id
,s.OVER_SHIPMENT_TOLERANCE
,s.UNDER_SHIPMENT_TOLERANCE
,s.ITEM_CROSS_REF_PREF
,s.dates_positive_tolerance
,s.date_type_preference
,o.transaction_type_id
,sm.lookup_code
,fp.lookup_code
,ft.lookup_code
,dc.lookup_code
,'Y'
INTO G_SHIP_TO_TBL(p_key).ship_to_org_id
,G_SHIP_TO_TBL(p_key).customer_id
,G_SHIP_TO_TBL(p_key).ship_from_org_id
,G_SHIP_TO_TBL(p_key).ship_tolerance_above
,G_SHIP_TO_TBL(p_key).ship_tolerance_below
,G_SHIP_TO_TBL(p_key).item_identifier_type
,G_SHIP_TO_TBL(p_key).latest_schedule_limit
,G_SHIP_TO_TBL(p_key).order_date_type_code
,G_SHIP_TO_TBL(p_key).order_type_id
,G_SHIP_TO_TBL(p_key).shipping_method_code
,G_SHIP_TO_TBL(p_key).fob_point_code
,G_SHIP_TO_TBL(p_key).freight_terms_code
,G_SHIP_TO_TBL(p_key).demand_class_code
,G_SHIP_TO_TBL(p_key).default_attributes
FROM hz_cust_site_uses_all s
,hz_cust_acct_sites_all a -- changed to _all since we know site_use_id and to perform better.
,oe_transaction_types_all o
,oe_ship_methods_v sm
,oe_ar_lookups_v fp
,oe_lookups ft
,oe_fnd_common_lookups_v dc
WHERE s.site_use_id = p_key
AND a.cust_acct_site_id = s.cust_acct_site_id
AND s.site_use_code = 'SHIP_TO'
AND s.status = 'A'
AND a.status ='A' --bug 2752321
AND s.order_type_id = o.transaction_type_id(+)
AND sysdate between nvl(o.start_date_active(+),sysdate)
and nvl(o.end_date_active(+),sysdate)
AND s.ship_via = sm.lookup_code(+)
AND sm.lookup_type(+) = 'SHIP_METHOD'
AND sm.enabled_flag(+) = 'Y'
AND sysdate between nvl(sm.start_date_active(+),sysdate)
and nvl(sm.end_date_active(+),sysdate)
AND s.fob_point = fp.lookup_code(+)
AND fp.lookup_type(+) = 'FOB'
AND fp.enabled_flag(+) = 'Y'
AND sysdate between nvl(fp.start_date_active(+),sysdate)
and nvl(fp.end_date_active(+),sysdate)
AND s.freight_term = ft.lookup_code(+)
AND ft.lookup_type(+) = 'FREIGHT_TERMS'
AND ft.enabled_flag(+) = 'Y'
AND sysdate between nvl(ft.start_date_active(+),sysdate)
and nvl(ft.end_date_active(+),sysdate)
AND s.demand_class_code = dc.lookup_code(+)
AND dc.lookup_type(+) = 'DEMAND_CLASS'
AND dc.enabled_flag(+) = 'Y'
AND sysdate between nvl(dc.start_date_active(+),sysdate)
and nvl(dc.end_date_active(+),sysdate);
SELECT b.cust_acct_site_id
,a.ece_tp_location_code
,b.location
INTO G_SHIP_TO_TBL(p_key).address_id
,G_SHIP_TO_TBL(p_key).edi_location_code
,G_SHIP_TO_TBL(p_key).location
FROM hz_cust_acct_sites_all a
, hz_cust_site_uses_all b
WHERE a.cust_acct_site_id = b.cust_acct_site_id
AND b.site_use_id = p_key
AND b.site_use_code='SHIP_TO';
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_SHIP_TO_TBL.DELETE(p_key);
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_SHIP_TO_TBL.DELETE(p_key);
SELECT /* MOAC_SQL_CHANGE */ a.cust_acct_site_id
INTO G_SOLD_TO_TBL(p_key).address_id
FROM hz_cust_site_uses_all b, hz_cust_acct_sites_all a
WHERE a.cust_acct_site_id = b.cust_acct_site_id
AND a.cust_account_id = p_key
/* AND NVL(a.org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),
1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),
' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99) */
And a.org_id = l_org_id
AND b.site_use_code = 'SOLD_TO'
AND b.primary_flag = 'Y'
AND b.status = 'A'
AND a.status = 'A';--bug 2752321
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_SOLD_TO_TBL.DELETE(p_key);
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_SOLD_TO_TBL.DELETE(p_key);
SELECT a.cust_acct_site_id
FROM hz_cust_site_uses_all b, hz_cust_acct_sites_all a
WHERE a.cust_acct_site_id = b.cust_acct_site_id
AND a.cust_account_id = p_key
AND NVL(a.org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
AND b.site_use_code = p_site_use_code
AND b.primary_flag = 'Y'
AND b.status = 'A'
AND a.status = 'A';
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_END_CUSTOMER_TBL.DELETE(p_key);
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_END_CUSTOMER_TBL.DELETE(p_key);
SELECT s.site_use_id
,a.cust_account_id
from hz_cust_site_uses s
,hz_cust_acct_sites a
WHERE s.site_use_id = p_key
AND s.site_use_code =p_site_use_code
AND s.cust_acct_site_id = a.cust_acct_site_id
AND s.status = 'A'
AND a.status ='A';
SELECT b.cust_acct_site_id
,a.ece_tp_location_code
,b.location
FROM hz_cust_acct_sites_all a
, hz_cust_site_uses_all b
WHERE a.cust_acct_site_id = b.cust_acct_site_id
AND b.site_use_id = p_key
AND b.site_use_code=p_site_use_code;
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_SOLD_TO_SITE_TBL.DELETE(p_key);
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_SOLD_TO_SITE_TBL.DELETE(p_key);
SELECT /* MOAC_SQL_CHANGE */ s.site_use_id
,a.cust_account_id
INTO G_INVOICE_TO_TBL(p_key).invoice_to_org_id
,G_INVOICE_TO_TBL(p_key).customer_id
FROM hz_cust_site_uses_all s
,hz_cust_acct_sites a
WHERE s.site_use_id = p_key
AND s.site_use_code = 'BILL_TO'
AND s.cust_acct_site_id = a.cust_acct_site_id
AND s.status = 'A'
AND a.status ='A'; --bug 2752321
SELECT /* MOAC_SQL_CHANGE */ s.site_use_id
,a.cust_account_id
,o.transaction_type_id
,term.term_id
,pl.list_header_id
,'Y'
INTO G_INVOICE_TO_TBL(p_key).invoice_to_org_id
,G_INVOICE_TO_TBL(p_key).customer_id
,G_INVOICE_TO_TBL(p_key).order_type_id
,G_INVOICE_TO_TBL(p_key).payment_term_id
,G_INVOICE_TO_TBL(p_key).price_list_id
,G_INVOICE_TO_TBL(p_key).default_attributes
FROM hz_cust_site_uses_all s
,hz_cust_acct_sites a
,oe_transaction_types_all o
,ra_terms_b term
,qp_list_headers_b pl
WHERE s.site_use_id = p_key
AND s.site_use_code = 'BILL_TO'
AND s.cust_acct_site_id = a.cust_acct_site_id
AND s.status = 'A'
AND a.status ='A'--bug 2752321
AND s.order_type_id = o.transaction_type_id(+)
AND sysdate between nvl(o.start_date_active(+),sysdate)
and nvl(o.end_date_active(+),sysdate)
AND s.payment_term_id = term.term_id(+)
AND sysdate between nvl(term.start_date_active(+),sysdate)
and nvl(term.end_date_active(+),sysdate)
AND s.price_list_id = pl.list_header_id(+)
AND nvl(pl.active_flag(+),'Y') = 'Y'
;
SELECT b.cust_acct_site_id
,a.ece_tp_location_code
,b.location
INTO G_INVOICE_TO_TBL(p_key).address_id
,G_INVOICE_TO_TBL(p_key).edi_location_code
,G_INVOICE_TO_TBL(p_key).location
FROM hz_cust_acct_sites_all a
, hz_cust_site_uses_all b
WHERE a.cust_acct_site_id = b.cust_acct_site_id
AND b.site_use_id = p_key
AND b.site_use_code='BILL_TO';
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_INVOICE_TO_TBL.DELETE(p_key);
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_INVOICE_TO_TBL.DELETE(p_key);
SELECT /* MOAC_SQL_CHANGE */ s.site_use_id
,a.cust_account_id
INTO G_SOLD_TO_SITE_TBL(p_key).sold_to_site_use_id
,G_SOLD_TO_SITE_TBL(p_key).customer_id
FROM hz_cust_site_uses_all s
,hz_cust_acct_sites a
WHERE s.site_use_id = p_key
AND s.site_use_code = 'SOLD_TO'
AND s.cust_acct_site_id = a.cust_acct_site_id
AND s.status = 'A'
AND a.status ='A';
SELECT /* MOAC_SQL_CHANGE */ s.site_use_id
,a.cust_account_id
,o.transaction_type_id
,term.term_id
,pl.list_header_id
,'Y'
INTO G_SOLD_TO_SITE_TBL(p_key).sold_to_site_use_id
,G_SOLD_TO_SITE_TBL(p_key).customer_id
,G_SOLD_TO_SITE_TBL(p_key).order_type_id
,G_SOLD_TO_SITE_TBL(p_key).payment_term_id
,G_SOLD_TO_SITE_TBL(p_key).price_list_id
,G_SOLD_TO_SITE_TBL(p_key).default_attributes
FROM hz_cust_site_uses_all s
,hz_cust_acct_sites a
,oe_transaction_types_all o
,ra_terms_b term
,qp_list_headers_b pl
WHERE s.site_use_id = p_key
AND s.site_use_code = 'SOLD_TO'
AND s.cust_acct_site_id = a.cust_acct_site_id
AND s.status = 'A'
AND a.status ='A'--bug 2752321
AND s.order_type_id = o.transaction_type_id(+)
AND sysdate between nvl(o.start_date_active(+),sysdate)
and nvl(o.end_date_active(+),sysdate)
AND s.payment_term_id = term.term_id(+)
AND sysdate between nvl(term.start_date_active(+),sysdate)
and nvl(term.end_date_active(+),sysdate)
AND s.price_list_id = pl.list_header_id(+)
AND nvl(pl.active_flag(+),'Y') = 'Y'
;
SELECT b.cust_acct_site_id
,a.ece_tp_location_code
,b.location
INTO G_SOLD_TO_SITE_TBL(p_key).address_id
,G_SOLD_TO_SITE_TBL(p_key).edi_location_code
,G_SOLD_TO_SITE_TBL(p_key).location
FROM hz_cust_acct_sites_all a
, hz_cust_site_uses_all b
WHERE a.cust_acct_site_id = b.cust_acct_site_id
AND b.site_use_id = p_key
AND b.site_use_code='SOLD_TO';
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_SOLD_TO_SITE_TBL.DELETE(p_key);
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_SOLD_TO_SITE_TBL.DELETE(p_key);
SELECT salesrep_id
,sales_credit_type_id
,person_id
,sales_tax_geocode
,sales_tax_inside_city_limits
INTO G_SALESREP_TBL(p_key).salesrep_id
,G_SALESREP_TBL(p_key).sales_credit_type_id
,G_SALESREP_TBL(p_key).person_id
,G_SALESREP_TBL(p_key).sales_tax_geocode
,G_SALESREP_TBL(p_key).sales_tax_inside_city_limits
FROM RA_SALESREPS s
WHERE SALESREP_ID = p_key;
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_SALESREP_TBL.DELETE(p_key);
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_SALESREP_TBL.DELETE(p_key);
SELECT hu.location_id,hl.ece_tp_location_code, hl.location_code
INTO l_addr_id, l_location_code,l_addr_code
FROM hr_all_organization_units hu,
hr_locations hl
WHERE hl.location_id = hu.location_id
AND hu.organization_id = p_key;
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_SHIP_FROM_TBL.DELETE(p_key);
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_SHIP_FROM_TBL.DELETE(p_key);
SELECT list_header_id
,name
,list_type_code
,start_date_active
,end_date_active
,currency_code
INTO G_PRICE_LIST_TBL(p_key).price_list_id
,G_PRICE_LIST_TBL(p_key).name
,G_PRICE_LIST_TBL(p_key).list_type_code
,G_PRICE_LIST_TBL(p_key).start_date_active
,G_PRICE_LIST_TBL(p_key).end_date_active
,G_PRICE_LIST_TBL(p_key).currency_code
FROM qp_list_headers_vl
WHERE list_header_id = p_key;
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_PRICE_LIST_TBL.DELETE(p_key);
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_PRICE_LIST_TBL.DELETE(p_key);
SELECT SU.SITE_USE_ID,
SU.CUST_ACCT_SITE_ID,
ACCT_SITE.CUST_ACCOUNT_ID,
LOC.POSTAL_CODE,
LOC.LOCATION_ID,
PARTY.PARTY_ID ,
PARTY.PARTY_NAME,
PARTY_SITE.PARTY_SITE_ID,
CUST_ACCT.ACCOUNT_NUMBER,
CUST_ACCT.TAX_HEADER_LEVEL_FLAG ACCT_TAX_HEADER_LEVEL_FLAG,
CUST_ACCT.TAX_ROUNDING_RULE ACCT_TAX_ROUNDING_RULE,
LOC.STATE,
SU.TAX_HEADER_LEVEL_FLAG SU_TAX_HEADER_LEVEL_FLAG,
SU.TAX_ROUNDING_RULE SU_TAX_ROUNDING_RULE
INTO
G_LOC_INFO_TBL(p_key).site_use_id,
G_LOC_INFO_TBL(p_key).cust_acct_site_id,
G_LOC_INFO_TBL(p_key).cust_account_id,
G_LOC_INFO_TBL(p_key).postal_code,
G_LOC_INFO_TBL(p_key).loc_id,
G_LOC_INFO_TBL(p_key).party_id,
G_LOC_INFO_TBL(p_key).party_name,
G_LOC_INFO_TBL(p_key).party_site_id,
G_LOC_INFO_TBL(p_key).account_number,
G_LOC_INFO_TBL(p_key).acct_tax_header_level_flag,
G_LOC_INFO_TBL(p_key).acct_tax_rounding_rule,
G_LOC_INFO_TBL(p_key).state,
G_LOC_INFO_TBL(p_key).tax_header_level_flag,
G_LOC_INFO_TBL(p_key).tax_rounding_rule
FROM
HZ_CUST_SITE_USES_ALL SU ,
HZ_CUST_ACCT_SITES ACCT_SITE,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOCATIONS LOC,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_PARTIES PARTY,
HZ_CUST_ACCOUNTS CUST_ACCT
WHERE SU.SITE_USE_ID = p_key
AND SU.CUST_ACCT_SITE_ID = acct_site.cust_acct_site_id
and acct_site.cust_account_id = cust_acct.cust_account_id
and cust_acct.party_id = party.party_id
and acct_site.party_site_id = party_site.party_site_id
and party_site.location_id = loc.location_id
and loc.location_id = loc_assign.location_id
/*AND NVL(acct_site.org_id,
NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1 ,1), ' ',NULL,
SUBSTRB(USERENV('CLIENT_INFO'), 1,10))),-99)) =
NVL(loc_assign.org_id,
NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))), -99)) */
and NVL(acct_site.org_id, l_org_id) = NVL (loc_assign.org_id, l_org_id);
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_LOC_INFO_TBL.DELETE(p_key);
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_LOC_INFO_TBL.DELETE(p_key);
SELECT V.AMOUNT_INCLUDES_TAX_FLAG,
V.TAXABLE_BASIS TAXABLE_BASIS,
V.TAX_CALCULATION_PLSQL_BLOCK,
V.VAT_TAX_ID
INTO G_TAX_ATTRIBUTES_TBL(1).AMOUNT_INCLUDES_TAX_FLAG,
G_TAX_ATTRIBUTES_TBL(1).TAXABLE_BASIS,
G_TAX_ATTRIBUTES_TBL(1).TAX_CALCULATION_PLSQL_BLOCK,
G_TAX_ATTRIBUTES_TBL(1).VAT_TAX_ID
FROM AR_VAT_TAX V
WHERE V.TAX_CODE = p_key
AND trunc(p_tax_date)
BETWEEN trunc(V.START_DATE)
AND NVL(trunc(V.END_DATE),trunc(p_tax_date))
AND V.TAX_CLASS = 'O'
AND NVL(V.ENABLED_FLAG,'Y') = 'Y'
AND V.SET_OF_BOOKS_ID = OE_BULK_ORDER_PVT.G_SOB_ID;
SELECT ASGN.ORGANIZATION_ID,
HOU.LOCATION_ID,
nvl(ASGN.EFFECTIVE_START_DATE,TO_DATE( '01011900',
'DDMMYYYY')),
nvl(ASGN.EFFECTIVE_END_DATE,TO_DATE( '31122199', 'DDMMYYYY'))
INTO G_PERSON_TBL(p_key).organization_id,
G_PERSON_TBL(p_key).location_id,
G_PERSON_TBL(p_key).start_date,
G_PERSON_TBL(p_key).end_date
FROM PER_ALL_ASSIGNMENTS_F ASGN,
hr_organization_units hou
WHERE ASGN.PERSON_ID = p_key
AND NVL(ASGN.PRIMARY_FLAG, 'Y') = 'Y'
AND hou.organization_id = ASGN.ORGANIZATION_ID
AND p_tax_date
BETWEEN nvl(ASGN.EFFECTIVE_START_DATE,TO_DATE( '01011900'
, 'DDMMYYYY'))
AND nvl(ASGN.EFFECTIVE_END_DATE,TO_DATE( '31122199', 'DDMMYYYY'))
AND ASSIGNMENT_TYPE = 'E';
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_PERSON_TBL.DELETE(p_key);
oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
G_PERSON_TBL.DELETE(p_key);