[Home] [Help]
23: ) IS
24: l_Charge_Details_rec CS_Charge_Details_PUB.Charges_Rec_Type;
25: l_Charge_Details_rec_upd CS_Charge_Details_PUB.Charges_Rec_Type;
26:
27: l_prod_txns_rec CSD_PRODUCT_TRANSACTIONS%ROWTYPE;
28: -- default out params.
29: x_return_status VARCHAR2(5);
30: x_msg_data VARCHAR2(2000);
31: x_msg_count NUMBER;
213: End if;
214: raise l_create_charge_err;
215: end if;
216:
217: -- need to create the corresponding record in the CSD_PRODUCT_TRANSACTIONS table.
218: -- Essentially all the values will be same as the existing record minus
219: -- estimate_detail_id and PROD_TXN_STATUS.
220: begin
221: select
218: -- Essentially all the values will be same as the existing record minus
219: -- estimate_detail_id and PROD_TXN_STATUS.
220: begin
221: select
222: CSD_PRODUCT_TRANSACTIONS_S1.nextval,
223: REPAIR_LINE_ID,
224: ACTION_TYPE,
225: ACTION_CODE,
226: LOT_NUMBER,
314: l_prod_txns_rec.PROJECT_ID,
315: l_prod_txns_rec.TASK_ID,
316: l_prod_txns_rec.UNIT_NUMBER,
317: l_prod_txns_rec.INTERNAL_PO_HEADER_ID
318: from csd_product_transactions
319: where estimate_detail_id = l_charge_details_rec_upd.estimate_detail_id;
320: exception
321: when no_data_found then
322: -- somebody removed the row. should not happen.
328: fnd_log.STRING (fnd_log.level_procedure,
329: 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.AFTER_UNDER_RECEIPT_PRCS',
330: 'Under-receipt: before calling insert: product txn id :'||l_prod_txns_rec.product_transaction_id);
331: End if;
332: -- insert the values into csd_product_transactions.
333: INSERT INTO CSD_PRODUCT_TRANSACTIONS(
334: PRODUCT_TRANSACTION_ID,
335: REPAIR_LINE_ID,
336: ESTIMATE_DETAIL_ID,
329: 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.AFTER_UNDER_RECEIPT_PRCS',
330: 'Under-receipt: before calling insert: product txn id :'||l_prod_txns_rec.product_transaction_id);
331: End if;
332: -- insert the values into csd_product_transactions.
333: INSERT INTO CSD_PRODUCT_TRANSACTIONS(
334: PRODUCT_TRANSACTION_ID,
335: REPAIR_LINE_ID,
336: ESTIMATE_DETAIL_ID,
337: ACTION_TYPE,
1829: and a.header_id = p_order_header_id;
1830:
1831: Cursor c_get_prod_txn_status ( p_repair_line_id number ) is
1832: Select prod_txn_status
1833: from csd_product_transactions
1834: where repair_line_id = p_repair_line_id
1835: and action_type = 'RMA';
1836:
1837: BEGIN
2632: ' and sr.customer_id = :p_party_id '||
2633: ' AND cr.repair_type_id = crt.repair_type_id '||
2634: ' and not exists ( '||
2635: ' select repair_line_id '||
2636: ' from csd_product_transactions cpt '||
2637: ' where crt.repair_type_ref <> ''ARR'' '||
2638: ' and cpt.repair_line_id = cr.repair_line_id '||
2639: ' and cpt.action_type = ''RMA'' '||
2640: ' and cpt.prod_txn_status in (''CANCELLED'' ,''RECEIVED'') '||
2639: ' and cpt.action_type = ''RMA'' '||
2640: ' and cpt.prod_txn_status in (''CANCELLED'' ,''RECEIVED'') '||
2641: ' UNION ALL '||
2642: ' SELECT repair_line_id '||
2643: ' from csd_product_transactions cpt1' ||
2644: ' where crt.repair_type_ref = ''ARR''' ||
2645: ' AND cpt1.repair_line_id = cr.repair_line_id '||
2646: ' AND cpt1.action_type = ''RMA'' '||
2647: ' AND ((cpt1.action_code = ''LOANER'' AND cpt1.prod_txn_status IN (''CANCELLED'',''RECEIVED''))AND ' ||
2791: and sr.account_id = x_sr_ro_rma_tbl(l_counter).cust_acct_id
2792: and sr.customer_id = x_sr_ro_rma_tbl(l_counter).party_id
2793: and not exists (
2794: select repair_line_id
2795: from csd_product_transactions cpt
2796: where cpt.repair_line_id = cr.repair_line_id
2797: and cpt.action_type = 'RMA'
2798: and cpt.prod_txn_status in ('RECEIVED','CANCELLED'))
2799: and cr.quantity = x_sr_ro_rma_tbl(l_counter).quantity
2833: x_sr_ro_rma_tbl(l_counter).repair_line_id,
2834: x_sr_ro_rma_tbl(l_counter).create_sr_flag,
2835: x_sr_ro_rma_tbl(l_counter).create_ro_flag
2836: from csd_repairs cr,
2837: csd_product_transactions cpt,
2838: cs_incidents_all_b sr,
2839: cs_estimate_details ced
2840: where cr.incident_id = sr.incident_id
2841: and sr.account_id = x_sr_ro_rma_tbl(l_counter).cust_acct_id
2847: and cpt.action_type = 'SHIP'
2848: and cpt.action_code = 'LOANER'
2849: and cpt.prod_txn_status = 'SHIPPED'
2850: and not exists
2851: ( select 'Y' from csd_product_transactions cpt1
2852: where cpt1.repair_line_id = cpt.repair_line_id
2853: and cpt1.action_type = 'RMA'
2854: and cpt1.action_code = 'LOANER'
2855: and cpt1.prod_txn_status in ('RECEIVED','CANCELLED')
2889: and sr.account_id = x_sr_ro_rma_tbl(l_counter).cust_acct_id
2890: and sr.customer_id = x_sr_ro_rma_tbl(l_counter).party_id
2891: and not exists (
2892: select repair_line_id
2893: from csd_product_transactions cpt
2894: where cpt.repair_line_id = cr.repair_line_id
2895: and cpt.action_type = 'RMA'
2896: and cpt.prod_txn_status in ('RECEIVED','CANCELLED'))
2897: and cr.repair_line_id not in (
2932: x_sr_ro_rma_tbl(l_counter).repair_line_id,
2933: x_sr_ro_rma_tbl(l_counter).create_sr_flag,
2934: x_sr_ro_rma_tbl(l_counter).create_ro_flag
2935: FROM csd_repairs cr,
2936: csd_product_transactions cpt,
2937: cs_incidents_all_b sr
2938: WHERE cr.incident_id = sr.incident_id
2939: AND sr.account_id = x_sr_ro_rma_tbl(l_counter).cust_acct_id
2940: AND sr.customer_id = x_sr_ro_rma_tbl(l_counter).party_id
2943: AND cpt.action_type = 'SHIP'
2944: AND cpt.action_code = 'LOANER'
2945: AND cpt.prod_txn_status = 'SHIPPED'
2946: AND NOT EXISTS
2947: ( SELECT 'Y' FROM csd_product_transactions cpt1
2948: WHERE cpt1.repair_line_id = cpt.repair_line_id
2949: AND cpt1.action_type = 'RMA'
2950: AND cpt1.action_code = 'LOANER'
2951: AND cpt1.prod_txn_status in ('RECEIVED','CANCELLED')
3046: ool.line_id,
3047: ool.inventory_item_id
3048: bulk collect into
3049: l_matching_rma_tbl
3050: from csd_product_transactions cpt,
3051: cs_estimate_details ced,
3052: oe_order_lines_all ool,
3053: csd_repairs cr,
3054: csd_repair_types_b crtb
3088: l_prod_txn_rec.ship_to_org_id,
3089: l_prod_txn_rec.organization_id,
3090: l_prod_txn_rec.inventory_org_id
3091:
3092: from csd_product_transactions cpt,
3093: cs_estimate_details ced
3094: where cpt.repair_line_id = p_repair_line_id and
3095: cpt.action_type = 'RMA' and
3096: cpt.action_code in ('CUST_PROD','EXCHANGE') and
3118: ool.line_id,
3119: ool.inventory_item_id
3120: bulk collect into
3121: l_matching_rma_tbl
3122: from csd_product_transactions cpt,
3123: cs_estimate_details ced,
3124: oe_order_lines_all ool,
3125: csd_repairs cr
3126: where
3137: if l_matching_rma_tbl.COUNT = 0 then
3138: begin
3139: select 'Y'
3140: into x_rma_found
3141: from csd_product_transactions cpt,
3142: cs_estimate_details ced
3143: where cpt.repair_line_id = p_repair_line_id and
3144: cpt.action_type = 'RMA' and
3145: cpt.action_code = 'LOANER' and
3262: l_prod_txn_rec.price_list_id,l_prod_txn_rec.txn_billing_type_id,
3263: l_prod_txn_rec.business_process_id,l_prod_txn_rec.currency_code,
3264: l_prod_txn_rec.ship_to_party_id,l_prod_txn_rec.ship_to_account_id,
3265: l_prod_txn_rec.return_reason,l_prod_txn_rec.contract_line_id
3266: from csd_product_transactions cpt,
3267: cs_estimate_details ced
3268: where cpt.repair_line_id = p_repair_line_id and
3269: ced.estimate_detail_id = cpt.estimate_detail_id and
3270: cpt.action_type IN ('RMA','RMA_THIRD_PARTY') and
3309: select ced.order_line_id,
3310: ced.order_header_id
3311: into x_order_line_id,
3312: x_order_header_id
3313: from cs_estimate_details ced,csd_product_transactions cpt
3314: where cpt.product_transaction_id = l_prod_txn_rec.product_transaction_id
3315: and ced.estimate_detail_id = cpt.estimate_detail_id;
3316: exception
3317: when no_data_found then
3542: and a.header_id = p_order_header_id;
3543:
3544: Cursor c_get_prod_txn_status ( p_repair_line_id number ) is
3545: Select prod_txn_status
3546: from csd_product_transactions
3547: where repair_line_id = p_repair_line_id
3548: and action_type = 'RMA';
3549:
3550: l_rcv_error_msg_tbl csd_receive_util.rcv_error_msg_tbl;
3597: cbr.rcv_attribute15
3598: bulk collect into
3599: l_bulk_autorcv_tbl
3600: from csd_bulk_receive_items_b cbr,
3601: csd_product_transactions cpt,
3602: cs_estimate_details ced
3603: where cbr.transaction_number = p_transaction_number
3604: and cbr.repair_line_id = cpt.repair_line_id
3605: and cpt.action_type = 'RMA'
3905:
3906: if l_order_header_id is not null and l_order_line_id is not null then
3907: select cpt.prod_txn_status
3908: into l_rma_status
3909: from csd_product_transactions cpt,
3910: cs_estimate_details ced
3911: where cpt.repair_line_id = p_repair_line_id
3912: and cpt.estimate_detail_id = ced.estimate_detail_id
3913: and ced.order_header_id = l_order_header_id
3962: l_prod_txn_rec.order_header_id,
3963: l_prod_txn_rec.order_line_id,
3964: l_prod_txn_rec.project_id,
3965: l_prod_txn_rec.unit_number
3966: from csd_product_transactions cpt,
3967: cs_estimate_details ced
3968: where cpt.repair_line_id = p_repair_line_id and
3969: cpt.action_type = 'RMA' and
3970: cpt.action_code = 'CUST_PROD' and
4011: l_prod_txn_rec.bill_to_account_id,
4012: l_prod_txn_rec.order_header_id,
4013: l_prod_txn_rec.order_line_id,
4014: l_prod_txn_rec.project_id
4015: from csd_product_transactions cpt,
4016: cs_estimate_details ced
4017: where cpt.repair_line_id = p_repair_line_id and
4018: cpt.action_type = 'RMA' and
4019: cpt.action_code = 'LOANER' and
4044:
4045: if l_add_to_order_ro = 'Y' then
4046: Select max(ced.order_header_id)
4047: into l_prod_txn_rec.add_to_order_id
4048: from csd_product_transactions cpt,
4049: cs_estimate_details ced,
4050: oe_order_headers_all ooh,
4051: oe_order_types_v oot,
4052: cs_incidents_all_b sr
4113: end if;
4114:
4115: select ced.order_header_id,ced.order_line_id
4116: into px_order_header_id,px_order_line_id
4117: from csd_product_transactions cpt,
4118: cs_estimate_details ced
4119: where cpt.repair_line_id = p_repair_line_id
4120: and ced.estimate_detail_id = cpt.estimate_detail_id
4121: and ced.estimate_detail_id = l_prod_txn_rec.estimate_detail_id
4207: l_prod_txn_rec.price_list_id,l_prod_txn_rec.txn_billing_type_id,
4208: l_prod_txn_rec.business_process_id,l_prod_txn_rec.currency_code,
4209: l_prod_txn_rec.ship_to_party_id,l_prod_txn_rec.ship_to_account_id,
4210: l_prod_txn_rec.return_reason,l_prod_txn_rec.contract_line_id
4211: from csd_product_transactions cpt,
4212: cs_estimate_details ced
4213: where cpt.repair_line_id = p_repair_line_id and
4214: ced.estimate_detail_id = cpt.estimate_detail_id and
4215: cpt.action_type = 'SHIP' and