[Home] [Help]
31: ro_serial_number csd_repairs.serial_number%type ,
32: repair_number csd_repairs.repair_number%type,
33: ro_uom csd_repairs.unit_of_measure%type,
34: ro_item_id csd_repairs.inventory_item_id%type ,
35: product_transaction_id csd_product_transactions.product_transaction_id%type,
36: repair_line_id csd_product_transactions.repair_line_id%type,
37: action_code csd_product_transactions.action_code%type,
38: source_serial_number csd_product_transactions.source_serial_number%type,
39: source_instance_id csd_product_transactions.source_instance_id%type,
32: repair_number csd_repairs.repair_number%type,
33: ro_uom csd_repairs.unit_of_measure%type,
34: ro_item_id csd_repairs.inventory_item_id%type ,
35: product_transaction_id csd_product_transactions.product_transaction_id%type,
36: repair_line_id csd_product_transactions.repair_line_id%type,
37: action_code csd_product_transactions.action_code%type,
38: source_serial_number csd_product_transactions.source_serial_number%type,
39: source_instance_id csd_product_transactions.source_instance_id%type,
40: prod_txn_recd_qty csd_product_transactions.quantity_received%type,
33: ro_uom csd_repairs.unit_of_measure%type,
34: ro_item_id csd_repairs.inventory_item_id%type ,
35: product_transaction_id csd_product_transactions.product_transaction_id%type,
36: repair_line_id csd_product_transactions.repair_line_id%type,
37: action_code csd_product_transactions.action_code%type,
38: source_serial_number csd_product_transactions.source_serial_number%type,
39: source_instance_id csd_product_transactions.source_instance_id%type,
40: prod_txn_recd_qty csd_product_transactions.quantity_received%type,
41: estimate_quantity cs_estimate_details.quantity_required%type ,
34: ro_item_id csd_repairs.inventory_item_id%type ,
35: product_transaction_id csd_product_transactions.product_transaction_id%type,
36: repair_line_id csd_product_transactions.repair_line_id%type,
37: action_code csd_product_transactions.action_code%type,
38: source_serial_number csd_product_transactions.source_serial_number%type,
39: source_instance_id csd_product_transactions.source_instance_id%type,
40: prod_txn_recd_qty csd_product_transactions.quantity_received%type,
41: estimate_quantity cs_estimate_details.quantity_required%type ,
42: est_order_line_id cs_estimate_details.order_line_id%type ,
35: product_transaction_id csd_product_transactions.product_transaction_id%type,
36: repair_line_id csd_product_transactions.repair_line_id%type,
37: action_code csd_product_transactions.action_code%type,
38: source_serial_number csd_product_transactions.source_serial_number%type,
39: source_instance_id csd_product_transactions.source_instance_id%type,
40: prod_txn_recd_qty csd_product_transactions.quantity_received%type,
41: estimate_quantity cs_estimate_details.quantity_required%type ,
42: est_order_line_id cs_estimate_details.order_line_id%type ,
43: prod_txn_item_id cs_estimate_details.inventory_item_id%type
36: repair_line_id csd_product_transactions.repair_line_id%type,
37: action_code csd_product_transactions.action_code%type,
38: source_serial_number csd_product_transactions.source_serial_number%type,
39: source_instance_id csd_product_transactions.source_instance_id%type,
40: prod_txn_recd_qty csd_product_transactions.quantity_received%type,
41: estimate_quantity cs_estimate_details.quantity_required%type ,
42: est_order_line_id cs_estimate_details.order_line_id%type ,
43: prod_txn_item_id cs_estimate_details.inventory_item_id%type
44: );
44: );
45:
46: TYPE IO_RCPT_LINES_Rec_Type IS RECORD
47: (
48: product_transaction_id csd_product_transactions.PRODUCT_TRANSACTION_ID%type,
49: prod_txn_status csd_product_transactions.PROD_TXN_STATUS%type,
50: repair_line_id csd_product_transactions.REPAIR_LINE_ID%type,
51: order_header_id csd_product_transactions.ORDER_HEADER_ID%type,
52: order_line_id csd_product_transactions.ORDER_LINE_ID%type,
45:
46: TYPE IO_RCPT_LINES_Rec_Type IS RECORD
47: (
48: product_transaction_id csd_product_transactions.PRODUCT_TRANSACTION_ID%type,
49: prod_txn_status csd_product_transactions.PROD_TXN_STATUS%type,
50: repair_line_id csd_product_transactions.REPAIR_LINE_ID%type,
51: order_header_id csd_product_transactions.ORDER_HEADER_ID%type,
52: order_line_id csd_product_transactions.ORDER_LINE_ID%type,
53: req_header_id csd_product_transactions.REQ_HEADER_ID%type,
46: TYPE IO_RCPT_LINES_Rec_Type IS RECORD
47: (
48: product_transaction_id csd_product_transactions.PRODUCT_TRANSACTION_ID%type,
49: prod_txn_status csd_product_transactions.PROD_TXN_STATUS%type,
50: repair_line_id csd_product_transactions.REPAIR_LINE_ID%type,
51: order_header_id csd_product_transactions.ORDER_HEADER_ID%type,
52: order_line_id csd_product_transactions.ORDER_LINE_ID%type,
53: req_header_id csd_product_transactions.REQ_HEADER_ID%type,
54: req_line_id csd_product_transactions.REPAIR_LINE_ID%type,
47: (
48: product_transaction_id csd_product_transactions.PRODUCT_TRANSACTION_ID%type,
49: prod_txn_status csd_product_transactions.PROD_TXN_STATUS%type,
50: repair_line_id csd_product_transactions.REPAIR_LINE_ID%type,
51: order_header_id csd_product_transactions.ORDER_HEADER_ID%type,
52: order_line_id csd_product_transactions.ORDER_LINE_ID%type,
53: req_header_id csd_product_transactions.REQ_HEADER_ID%type,
54: req_line_id csd_product_transactions.REPAIR_LINE_ID%type,
55: prod_txn_rcvd_qty csd_product_transactions.quantity_received%type,
48: product_transaction_id csd_product_transactions.PRODUCT_TRANSACTION_ID%type,
49: prod_txn_status csd_product_transactions.PROD_TXN_STATUS%type,
50: repair_line_id csd_product_transactions.REPAIR_LINE_ID%type,
51: order_header_id csd_product_transactions.ORDER_HEADER_ID%type,
52: order_line_id csd_product_transactions.ORDER_LINE_ID%type,
53: req_header_id csd_product_transactions.REQ_HEADER_ID%type,
54: req_line_id csd_product_transactions.REPAIR_LINE_ID%type,
55: prod_txn_rcvd_qty csd_product_transactions.quantity_received%type,
56: ro_qty csd_repairs.quantity%type,
49: prod_txn_status csd_product_transactions.PROD_TXN_STATUS%type,
50: repair_line_id csd_product_transactions.REPAIR_LINE_ID%type,
51: order_header_id csd_product_transactions.ORDER_HEADER_ID%type,
52: order_line_id csd_product_transactions.ORDER_LINE_ID%type,
53: req_header_id csd_product_transactions.REQ_HEADER_ID%type,
54: req_line_id csd_product_transactions.REPAIR_LINE_ID%type,
55: prod_txn_rcvd_qty csd_product_transactions.quantity_received%type,
56: ro_qty csd_repairs.quantity%type,
57: ro_rcvd_qty csd_repairs.quantity_rcvd%type,
50: repair_line_id csd_product_transactions.REPAIR_LINE_ID%type,
51: order_header_id csd_product_transactions.ORDER_HEADER_ID%type,
52: order_line_id csd_product_transactions.ORDER_LINE_ID%type,
53: req_header_id csd_product_transactions.REQ_HEADER_ID%type,
54: req_line_id csd_product_transactions.REPAIR_LINE_ID%type,
55: prod_txn_rcvd_qty csd_product_transactions.quantity_received%type,
56: ro_qty csd_repairs.quantity%type,
57: ro_rcvd_qty csd_repairs.quantity_rcvd%type,
58: inventory_item_id csd_repairs.inventory_item_id%type,
51: order_header_id csd_product_transactions.ORDER_HEADER_ID%type,
52: order_line_id csd_product_transactions.ORDER_LINE_ID%type,
53: req_header_id csd_product_transactions.REQ_HEADER_ID%type,
54: req_line_id csd_product_transactions.REPAIR_LINE_ID%type,
55: prod_txn_rcvd_qty csd_product_transactions.quantity_received%type,
56: ro_qty csd_repairs.quantity%type,
57: ro_rcvd_qty csd_repairs.quantity_rcvd%type,
58: inventory_item_id csd_repairs.inventory_item_id%type,
59: ro_uom csd_repairs.unit_of_measure%type,
84: repair_line_id csd_repairs.repair_line_id%type,
85: ro_uom csd_repairs.unit_of_measure%type ,
86: ro_item_id csd_repairs.inventory_item_id%type ,
87: estimate_quantity cs_estimate_details.quantity_required%type ,
88: prod_txn_serial_num csd_product_transactions.source_serial_number%type ,
89: source_instance_id csd_product_transactions.source_instance_id%type,
90: product_transaction_id csd_product_transactions.product_transaction_id%type,
91: action_code csd_product_transactions.action_code%type,
92: delivery_name wsh_new_deliveries.name%type ,
85: ro_uom csd_repairs.unit_of_measure%type ,
86: ro_item_id csd_repairs.inventory_item_id%type ,
87: estimate_quantity cs_estimate_details.quantity_required%type ,
88: prod_txn_serial_num csd_product_transactions.source_serial_number%type ,
89: source_instance_id csd_product_transactions.source_instance_id%type,
90: product_transaction_id csd_product_transactions.product_transaction_id%type,
91: action_code csd_product_transactions.action_code%type,
92: delivery_name wsh_new_deliveries.name%type ,
93: org_name hr_all_organization_units.name%type
86: ro_item_id csd_repairs.inventory_item_id%type ,
87: estimate_quantity cs_estimate_details.quantity_required%type ,
88: prod_txn_serial_num csd_product_transactions.source_serial_number%type ,
89: source_instance_id csd_product_transactions.source_instance_id%type,
90: product_transaction_id csd_product_transactions.product_transaction_id%type,
91: action_code csd_product_transactions.action_code%type,
92: delivery_name wsh_new_deliveries.name%type ,
93: org_name hr_all_organization_units.name%type
94: );
87: estimate_quantity cs_estimate_details.quantity_required%type ,
88: prod_txn_serial_num csd_product_transactions.source_serial_number%type ,
89: source_instance_id csd_product_transactions.source_instance_id%type,
90: product_transaction_id csd_product_transactions.product_transaction_id%type,
91: action_code csd_product_transactions.action_code%type,
92: delivery_name wsh_new_deliveries.name%type ,
93: org_name hr_all_organization_units.name%type
94: );
95: TYPE IO_SHIP_LINES_Rec_Type IS RECORD
2095: l_srl_ctl_code mtl_system_items.serial_number_control_code%type;
2096: l_lot_ctl_code mtl_system_items.lot_control_code%type;
2097: l_ib_flag mtl_system_items.comms_nl_trackable_flag%type;
2098: l_instance_id csi_item_instances.instance_id%type;
2099: l_prod_txn_status csd_product_transactions.prod_txn_status%type;
2100:
2101: -- activity record
2102: l_activity_rec activity_rec_type;
2103:
2140: cpt.quantity_received prod_txn_recd_qty,
2141: abs(ced.quantity_required) estimate_quantity,
2142: ced.order_line_id est_order_line_id,
2143: ced.inventory_item_id prod_txn_item_id
2144: FROM csd_product_transactions cpt,
2145: cs_estimate_details ced,
2146: csd_repairs cra,
2147: rcv_transactions rcvt,
2148: oe_order_headers_all oeh,
2217: cpt.quantity_received prod_txn_recd_qty,
2218: abs(ced.quantity_required) estimate_quantity,
2219: ced.order_line_id est_order_line_id,
2220: ced.inventory_item_id prod_txn_item_id
2221: FROM csd_product_transactions cpt,
2222: cs_estimate_details ced,
2223: csd_repairs cra,
2224: rcv_transactions rcvt,
2225: oe_order_headers_all oeh,
2295: oe_order_headers_all oeh,
2296: oe_order_lines_all oel,
2297: rcv_transactions rcvt,
2298: cs_estimate_details ced,
2299: csd_product_transactions cpt
2300: WHERE cpt.repair_line_id = p_repair_line_id
2301: AND cpt.action_type in ('RMA', 'RMA_THIRD_PTY') -- excluded walk-in-receipt as it is going off
2302: AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
2303: AND ced.order_header_id is not null
2340: CURSOR Cur_Cancelled_repair_lines IS
2341: SELECT cra.REPAIR_LINE_ID
2342: FROM csd_repairs cra,
2343: cs_estimate_details ced,
2344: csd_product_transactions cpt
2345: WHERE cpt.action_type in ('RMA', 'RMA_THIRD_PTY')
2346: AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
2347: AND ced.order_header_id is not null
2348: AND ced.source_code = 'DR'
2858: l_prod_txn_status := 'BOOKED';
2859: END IF;
2860:
2861: -- Update the quantity received,locator id, lot number
2862: Update csd_product_transactions
2863: set sub_inventory = I.subinventory,
2864: locator_id = I.locator_id,
2865: lot_number_rcvd = l_lot_number,
2866: source_instance_id = l_instance_id,
3091: l_serialized_flag boolean;
3092: l_ord_remaining_qty number := 0;
3093: l_prod_txn_exists boolean;
3094: l_prod_txn_id number := NULL;
3095: l_prod_txn_status csd_product_transactions.prod_txn_status%type;
3096: l_total_qty number;
3097: l_total_del_qty number;
3098: l_total_accept_qty number;
3099: l_total_reject_qty number;
3139: cra.unit_of_measure ro_uom,
3140: prh.segment1 requisition_number,
3141: oel.ordered_quantity,
3142: oeh.order_number
3143: from csd_product_transactions cpt,
3144: csd_repairs cra,
3145: po_requisition_headers_all prh,
3146: oe_order_lines_all oel,
3147: oe_order_headers_all oeh
3175: cra.unit_of_measure ro_uom,
3176: prh.segment1 requisition_number,
3177: oel.ordered_quantity,
3178: oeh.order_number
3179: from csd_product_transactions cpt,
3180: csd_repairs cra,
3181: po_requisition_headers_all prh,
3182: oe_order_lines_all oel,
3183: oe_order_headers_all oeh
3248: CURSOR ORDER_INFO (p_ord_header_id in number) IS
3249: Select distinct
3250: order_header_id,
3251: order_line_id
3252: from csd_product_transactions
3253: where order_header_id = p_ord_header_id
3254: AND action_type = 'MOVE_IN'
3255: AND action_code = 'DEFECTIVES'
3256: AND prod_txn_status = 'SHIPPED';
3283: -- Api body starts
3284:
3285: -- In case of Internal orders, the product txns are stamped
3286: -- with the order header id and line id.
3287: -- So Validate if it exists in csd_product_transactions
3288: IF NVL(p_order_header_id,-999) <> -999 THEN
3289: BEGIN
3290: select 'EXISTS'
3291: into l_dummy
3293: po_requisition_headers_all prh
3294: where oeh.source_document_id = prh.requisition_header_id
3295: and oeh.header_id = p_order_header_id
3296: and exists (select 'x'
3297: from csd_product_transactions cpt
3298: where cpt.action_type = 'MOVE_IN'
3299: and cpt.action_code = 'DEFECTIVES'
3300: and cpt.order_header_id = oeh.header_id);
3301: EXCEPTION
3521: where rcvt.transaction_id = DEL.transaction_id
3522: and rownum = 1
3523: and not exists (Select 'NOT EXIST'
3524: from csd_repairs cra,
3525: csd_product_transactions cpt
3526: where cra.repair_line_id = cpt.repair_line_id
3527: and cpt.action_type = 'MOVE_IN'
3528: and cpt.order_header_id = ro.order_header_id
3529: and cra.serial_number = rcvt.serial_num);
3655: -- Updating the product txn with the serial number,lot number
3656: -- qty rcvd and subinventory
3657: -- sub_inventory_rcvd is used for IO and subinventory column
3658: -- is used for the regular RMA
3659: UPDATE CSD_PRODUCT_TRANSACTIONS
3660: SET SOURCE_SERIAL_NUMBER = l_serial_num,
3661: source_instance_id = l_instance_id,
3662: LOT_NUMBER_RCVD = l_lot_num,
3663: LOCATOR_ID = DEL.locator_id,
3681: RAISE PROCESS_ERROR;
3682: END IF;
3683:
3684: IF RCV.serial_number_control_code in (2,5,6) THEN
3685: UPDATE CSD_PRODUCT_TRANSACTIONS
3686: SET prod_txn_status = 'RECEIVED',
3687: object_version_number = object_version_number+1,
3688: last_update_date = sysdate,
3689: last_updated_by = fnd_global.user_id,
3689: last_updated_by = fnd_global.user_id,
3690: last_update_login = fnd_global.login_id
3691: WHERE product_transaction_id = RO.product_transaction_id;
3692: ELSE
3693: UPDATE CSD_PRODUCT_TRANSACTIONS
3694: SET prod_txn_status = 'RECEIVED',
3695: object_version_number = object_version_number+1,
3696: last_update_date = sysdate,
3697: last_updated_by = fnd_global.user_id,
3889: where rcvt.transaction_id = DEL.transaction_id
3890: and rownum = 1
3891: and not exists (Select 'NOT EXIST'
3892: from csd_repairs cra,
3893: csd_product_transactions cpt
3894: where cra.repair_line_id = cpt.repair_line_id
3895: and cpt.action_type = 'MOVE_IN'
3896: and cpt.order_header_id = ro.order_header_id
3897: and cra.serial_number = rcvt.serial_num);
4024: -- Updating the product txn with the serial number,lot number
4025: -- qty rcvd and subinventory
4026: -- sub_inventory_rcvd is used for IO and subinventory column
4027: -- is used for the regular RMA
4028: UPDATE CSD_PRODUCT_TRANSACTIONS
4029: SET SOURCE_SERIAL_NUMBER = l_serial_num,
4030: source_instance_id = l_instance_id,
4031: LOT_NUMBER_RCVD = l_lot_num,
4032: LOCATOR_ID = DEL.locator_id,
4050: RAISE PROCESS_ERROR;
4051: END IF;
4052:
4053: IF RCV.serial_number_control_code in (2,5,6) THEN
4054: UPDATE CSD_PRODUCT_TRANSACTIONS
4055: SET prod_txn_status = 'RECEIVED',
4056: object_version_number = object_version_number+1,
4057: last_update_date = sysdate,
4058: last_updated_by = fnd_global.user_id,
4059: last_update_login = fnd_global.login_id
4060: WHERE product_transaction_id = RO.product_transaction_id;
4061:
4062: ELSE
4063: UPDATE CSD_PRODUCT_TRANSACTIONS
4064: SET prod_txn_status = 'RECEIVED',
4065: object_version_number = object_version_number+1,
4066: last_update_date = sysdate,
4067: last_updated_by = fnd_global.user_id,
4166: where rcvt.transaction_id = RCV.transaction_id
4167: and rownum = 1
4168: and not exists (Select 'NOT EXIST'
4169: from csd_repairs cra,
4170: csd_product_transactions cpt
4171: where cra.repair_line_id = cpt.repair_line_id
4172: and cpt.order_header_id = ro.order_header_id
4173: and cra.serial_number = rcvt.serial_num);
4174: Exception
4297:
4298:
4299: -- Update product txn with the rcvd serial number,lot number
4300: -- locator id, sub inv, status and the rcvd qty
4301: UPDATE CSD_PRODUCT_TRANSACTIONS
4302: SET SOURCE_SERIAL_NUMBER = l_serial_num,
4303: source_instance_id = l_instance_id,
4304: LOT_NUMBER_RCVD = l_lot_num,
4305: QUANTITY_RECEIVED = NVL(QUANTITY_RECEIVED,0) + l_rcvd_qty,
4323: RAISE PROCESS_ERROR;
4324: END IF;
4325:
4326: IF RCV.serial_number_control_code in (2,5,6) THEN
4327: UPDATE CSD_PRODUCT_TRANSACTIONS
4328: SET prod_txn_status = 'RECEIVED',
4329: object_version_number = object_version_number+1,
4330: last_update_date = sysdate,
4331: last_updated_by = fnd_global.user_id,
4331: last_updated_by = fnd_global.user_id,
4332: last_update_login = fnd_global.login_id
4333: WHERE product_transaction_id = RO.product_transaction_id;
4334: ELSE
4335: UPDATE CSD_PRODUCT_TRANSACTIONS
4336: SET prod_txn_status = 'RECEIVED',
4337: object_version_number = object_version_number+1,
4338: last_update_date = sysdate,
4339: last_updated_by = fnd_global.user_id,
4505: l_serialized_flag boolean;
4506: l_ord_remaining_qty number := 0;
4507: l_prod_txn_exists boolean;
4508: l_prod_txn_id number := NULL;
4509: l_prod_txn_status csd_product_transactions.prod_txn_status%type;
4510: l_total_qty number;
4511: l_total_del_qty number;
4512: l_total_accept_qty number;
4513: l_total_reject_qty number;
4553: cra.unit_of_measure ro_uom,
4554: prh.segment1 requisition_number,
4555: oel.ordered_quantity,
4556: oeh.order_number
4557: from csd_product_transactions cpt,
4558: csd_repairs cra,
4559: po_requisition_headers_all prh,
4560: oe_order_lines_all oel,
4561: oe_order_headers_all oeh
4589: cra.unit_of_measure ro_uom,
4590: prh.segment1 requisition_number,
4591: oel.ordered_quantity,
4592: oeh.order_number
4593: from csd_product_transactions cpt,
4594: csd_repairs cra,
4595: po_requisition_headers_all prh,
4596: oe_order_lines_all oel,
4597: oe_order_headers_all oeh
4664: CURSOR ORDER_INFO (p_ord_header_id in number) IS
4665: Select distinct
4666: order_header_id,
4667: order_line_id
4668: from csd_product_transactions
4669: where order_header_id = p_ord_header_id
4670: AND action_type = 'MOVE_OUT'
4671: AND action_code = 'USABLES'
4672: AND prod_txn_status = 'SHIPPED';
4699: -- Api body starts
4700:
4701: -- In case of Internal orders, the product txns are stamped
4702: -- with the order header id and line id.
4703: -- So Validate if it exists in csd_product_transactions
4704: IF NVL(p_order_header_id,-999) <> -999 THEN
4705: BEGIN
4706: select 'EXISTS'
4707: into l_dummy
4709: po_requisition_headers_all prh
4710: where oeh.source_document_id = prh.requisition_header_id
4711: and oeh.header_id = p_order_header_id
4712: and exists (select 'x'
4713: from csd_product_transactions cpt
4714: where cpt.action_type = 'MOVE_OUT'
4715: and cpt.action_code = 'USABLES'
4716: and cpt.order_header_id = oeh.header_id);
4717: EXCEPTION
4937: where rcvt.transaction_id = DEL.transaction_id
4938: and rownum = 1
4939: and not exists (Select 'NOT EXIST'
4940: from csd_repairs cra,
4941: csd_product_transactions cpt
4942: where cra.repair_line_id = cpt.repair_line_id
4943: and cpt.action_type = 'MOVE_OUT'
4944: and cpt.order_header_id = ro.order_header_id
4945: and cra.serial_number = rcvt.serial_num);
5076: -- Updating the product txn with the serial number,lot number
5077: -- qty rcvd and subinventory
5078: -- sub_inventory_rcvd is used for IO and subinventory column
5079: -- is used for the regular RMA
5080: UPDATE CSD_PRODUCT_TRANSACTIONS
5081: SET SOURCE_SERIAL_NUMBER = l_serial_num,
5082: source_instance_id = l_instance_id,
5083: LOT_NUMBER_RCVD = l_lot_num,
5084: LOCATOR_ID = DEL.locator_id,
5102: RAISE PROCESS_ERROR;
5103: END IF;
5104:
5105: IF RCV.serial_number_control_code in (2,5,6) THEN
5106: UPDATE CSD_PRODUCT_TRANSACTIONS
5107: SET prod_txn_status = 'RECEIVED',
5108: object_version_number = object_version_number+1,
5109: last_update_date = sysdate,
5110: last_updated_by = fnd_global.user_id,
5110: last_updated_by = fnd_global.user_id,
5111: last_update_login = fnd_global.login_id
5112: WHERE product_transaction_id = RO.product_transaction_id;
5113: ELSE
5114: UPDATE CSD_PRODUCT_TRANSACTIONS
5115: SET prod_txn_status = 'RECEIVED',
5116: object_version_number = object_version_number+1,
5117: last_update_date = sysdate,
5118: last_updated_by = fnd_global.user_id,
5310: where rcvt.transaction_id = DEL.transaction_id
5311: and rownum = 1
5312: and not exists (Select 'NOT EXIST'
5313: from csd_repairs cra,
5314: csd_product_transactions cpt
5315: where cra.repair_line_id = cpt.repair_line_id
5316: and cpt.action_type = 'MOVE_OUT'
5317: and cpt.order_header_id = ro.order_header_id
5318: and cra.serial_number = rcvt.serial_num);
5450: -- Updating the product txn with the serial number,lot number
5451: -- qty rcvd and subinventory
5452: -- sub_inventory_rcvd is used for IO and subinventory column
5453: -- is used for the regular RMA
5454: UPDATE CSD_PRODUCT_TRANSACTIONS
5455: SET SOURCE_SERIAL_NUMBER = l_serial_num,
5456: source_instance_id = l_instance_id,
5457: LOT_NUMBER_RCVD = l_lot_num,
5458: LOCATOR_ID = DEL.locator_id,
5476: RAISE PROCESS_ERROR;
5477: END IF;
5478:
5479: IF RCV.serial_number_control_code in (2,5,6) THEN
5480: UPDATE CSD_PRODUCT_TRANSACTIONS
5481: SET prod_txn_status = 'RECEIVED',
5482: object_version_number = object_version_number+1,
5483: last_update_date = sysdate,
5484: last_updated_by = fnd_global.user_id,
5485: last_update_login = fnd_global.login_id
5486: WHERE product_transaction_id = RO.product_transaction_id;
5487:
5488: ELSE
5489: UPDATE CSD_PRODUCT_TRANSACTIONS
5490: SET prod_txn_status = 'RECEIVED',
5491: object_version_number = object_version_number+1,
5492: last_update_date = sysdate,
5493: last_updated_by = fnd_global.user_id,
5600: --the serial number in the production transaction table. After commented this
5601: --condition, it worked for the customer.
5602: -- and not exists (Select 'NOT EXIST'
5603: -- from csd_repairs cra,
5604: -- csd_product_transactions cpt
5605: -- where cra.repair_line_id = cpt.repair_line_id
5606: -- and cpt.order_header_id = ro.order_header_id
5607: -- and cra.serial_number = rcvt.serial_num);
5608: Exception
5736: */
5737:
5738: -- Update product txn with the rcvd serial number,lot number
5739: -- locator id, sub inv, status and the rcvd qty
5740: UPDATE CSD_PRODUCT_TRANSACTIONS
5741: SET SOURCE_SERIAL_NUMBER = l_serial_num,
5742: source_instance_id = l_instance_id,
5743: LOT_NUMBER_RCVD = l_lot_num,
5744: QUANTITY_RECEIVED = NVL(QUANTITY_RECEIVED,0) + l_rcvd_qty,
5762: RAISE PROCESS_ERROR;
5763: END IF;
5764:
5765: IF RCV.serial_number_control_code in (2,5,6) THEN
5766: UPDATE CSD_PRODUCT_TRANSACTIONS
5767: SET prod_txn_status = 'RECEIVED',
5768: object_version_number = object_version_number+1,
5769: last_update_date = sysdate,
5770: last_updated_by = fnd_global.user_id,
5770: last_updated_by = fnd_global.user_id,
5771: last_update_login = fnd_global.login_id
5772: WHERE product_transaction_id = RO.product_transaction_id;
5773: ELSE
5774: UPDATE CSD_PRODUCT_TRANSACTIONS
5775: SET prod_txn_status = 'RECEIVED',
5776: object_version_number = object_version_number+1,
5777: last_update_date = sysdate,
5778: last_updated_by = fnd_global.user_id,
6178: cpt.action_code,
6179: wnd.name delivery_name,
6180: hao.name org_name
6181: from
6182: csd_product_transactions cpt,
6183: cs_estimate_details ced,
6184: csd_repairs cra,
6185: wsh_delivery_details dd ,
6186: wsh_serial_numbers dsn,--Added to fix 3801614
6254: cpt.action_code,
6255: wnd.name delivery_name,
6256: hao.name org_name
6257: from
6258: csd_product_transactions cpt,
6259: cs_estimate_details ced,
6260: csd_repairs cra,
6261: wsh_delivery_details dd ,
6262: wsh_serial_numbers dsn,--Added to fix 3801614
6338: cpt.action_code,
6339: wnd.name delivery_name,
6340: hao.name org_name
6341: from
6342: csd_product_transactions cpt,
6343: cs_estimate_details ced,
6344: csd_repairs cra,
6345: wsh_delivery_details dd ,
6346: wsh_serial_numbers dsn,--Added to fix 3801614
6382: CURSOR Cur_Cancelled_repair_lines IS
6383: SELECT cra.REPAIR_LINE_ID
6384: FROM csd_repairs cra,
6385: cs_estimate_details ced,
6386: csd_product_transactions cpt
6387: WHERE cpt.action_type in ('SHIP', 'SHIP_THIRD_PTY')
6388: AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
6389: AND ced.order_header_id is not null
6390: AND ced.source_code = 'DR'
6759:
6760:
6761: -- Updating the product txn with qty,subinventory,lot number
6762: -- locator id
6763: update csd_product_transactions
6764: set sub_inventory = i.subinv,
6765: lot_number = i.lot_number,
6766: quantity_shipped = nvl(quantity_shipped,0)+I.shipped_quantity,
6767: locator_id = i.locator_id,
6786: END IF;
6787:
6788: -- Updating the product txn with the status
6789: -- if the line qty is fully rcvd
6790: update csd_product_transactions
6791: set prod_txn_status = 'SHIPPED',
6792: object_version_number = object_version_number+1,
6793: last_update_date = sysdate,
6794: last_updated_by = fnd_global.user_id,
7053: and oel.inventory_item_id= mtl.inventory_item_id
7054: and dd.move_order_line_id = trl.line_id(+) -- Added to fix 4279958
7055: and dd.delivery_detail_id = dsn.delivery_detail_id(+) --Added to fix 3801614
7056: and exists (Select 'x'
7057: from csd_product_transactions cpt
7058: where cpt.order_header_id = oel.header_id
7059: and cpt.prod_txn_status in ('BOOKED','RELEASED'));
7060:
7061: -- New Cursor for all delivery lines.
7109: and oel.inventory_item_id= mtl.inventory_item_id
7110: and dd.move_order_line_id = trl.line_id(+) -- Added to fix 4279958
7111: and dd.delivery_detail_id = dsn.delivery_detail_id(+) --Added to fix 3801614
7112: and exists (Select 'x'
7113: from csd_product_transactions cpt
7114: where cpt.order_header_id = oel.header_id
7115: and cpt.prod_txn_status in ('BOOKED','RELEASED'));
7116:
7117:
7175: l_pt_line_qty number;
7176: l_pt_shipped_qty number;
7177: l_total_shipped_qty number;
7178: l_prod_txn_shipped_qty number;
7179: l_action_type csd_product_transactions.action_type%type;
7180: l_action_code csd_product_transactions.action_code%type;
7181: l_prod_txn_id number := NULL;
7182: l_prod_txn_status csd_product_transactions.prod_txn_status%type ;
7183: l_release_so_flag csd_product_transactions.release_sales_order_flag%type ;
7176: l_pt_shipped_qty number;
7177: l_total_shipped_qty number;
7178: l_prod_txn_shipped_qty number;
7179: l_action_type csd_product_transactions.action_type%type;
7180: l_action_code csd_product_transactions.action_code%type;
7181: l_prod_txn_id number := NULL;
7182: l_prod_txn_status csd_product_transactions.prod_txn_status%type ;
7183: l_release_so_flag csd_product_transactions.release_sales_order_flag%type ;
7184: l_ship_so_flag csd_product_transactions.ship_sales_order_flag%type ;
7178: l_prod_txn_shipped_qty number;
7179: l_action_type csd_product_transactions.action_type%type;
7180: l_action_code csd_product_transactions.action_code%type;
7181: l_prod_txn_id number := NULL;
7182: l_prod_txn_status csd_product_transactions.prod_txn_status%type ;
7183: l_release_so_flag csd_product_transactions.release_sales_order_flag%type ;
7184: l_ship_so_flag csd_product_transactions.ship_sales_order_flag%type ;
7185: l_qty_shipped number;
7186:
7179: l_action_type csd_product_transactions.action_type%type;
7180: l_action_code csd_product_transactions.action_code%type;
7181: l_prod_txn_id number := NULL;
7182: l_prod_txn_status csd_product_transactions.prod_txn_status%type ;
7183: l_release_so_flag csd_product_transactions.release_sales_order_flag%type ;
7184: l_ship_so_flag csd_product_transactions.ship_sales_order_flag%type ;
7185: l_qty_shipped number;
7186:
7187: -- activity record
7180: l_action_code csd_product_transactions.action_code%type;
7181: l_prod_txn_id number := NULL;
7182: l_prod_txn_status csd_product_transactions.prod_txn_status%type ;
7183: l_release_so_flag csd_product_transactions.release_sales_order_flag%type ;
7184: l_ship_so_flag csd_product_transactions.ship_sales_order_flag%type ;
7185: l_qty_shipped number;
7186:
7187: -- activity record
7188: l_activity_rec activity_rec_type;
7223: -- Api body starts
7224:
7225: -- In case of Internal orders, the product txns are stamped
7226: -- with the order header id and line id.
7227: -- So Validate if it exists in csd_product_transactions
7228: IF NVL(p_order_header_id,-999) <> -999 THEN
7229: BEGIN
7230: select 'EXISTS'
7231: into l_dummy
7233: po_requisition_headers_all prh
7234: where oeh.source_document_id = prh.requisition_header_id
7235: and oeh.header_id = p_order_header_id
7236: and exists (select 'x'
7237: from csd_product_transactions cpt
7238: where cpt.order_header_id = oeh.header_id );
7239: EXCEPTION
7240: WHEN NO_DATA_FOUND THEN
7241: IF ( l_error_level >= G_debug_level) THEN
7281: select action_type,
7282: action_code
7283: into l_action_type,
7284: l_action_code
7285: from csd_product_transactions
7286: where order_header_id = DEL.header_id
7287: and rownum = 1;
7288: EXCEPTION
7289: WHEN NO_DATA_FOUND THEN
7324: Debug('Processing the move-in lines ',l_mod_name,1);
7325:
7326: Select nvl(sum(quantity_shipped),0)
7327: into l_total_shipped_qty
7328: from csd_product_transactions
7329: where action_type = 'MOVE_IN'
7330: and action_code = 'DEFECTIVES'
7331: and order_line_id = DEL.line_id
7332: and order_header_id = DEL.header_id;
7357: Select product_transaction_id,
7358: repair_line_id
7359: into l_prod_txn_id,
7360: l_rep_line_id
7361: from csd_product_transactions
7362: where order_header_id = DEL.header_id
7363: and order_line_id = DEL.line_id
7364: and action_type = l_action_type
7365: and action_code = l_action_code;
7372: END;
7373:
7374: IF l_prod_txn_exists THEN
7375: --If product txn exist then update the shipped qty and the status
7376: UPDATE CSD_PRODUCT_TRANSACTIONS
7377: SET quantity_shipped = nvl(quantity_shipped,0) + nvl(DEL.shipped_quantity,0),
7378: sub_inventory = DEL.subinventory,
7379: lot_number = DEL.lot_number,
7380: locator_id = DEL.locator_id,
7405: -- Get the repair line id for the order header id
7406: Begin
7407: Select repair_line_id
7408: into l_rep_line_id
7409: from csd_product_transactions
7410: where order_header_id = DEL.header_id
7411: and action_type = l_action_type
7412: and action_code = l_action_code
7413: and rownum = 1;
7424: END IF;
7425: RAISE PROCESS_ERROR;
7426: End;
7427: Begin
7428: Debug('Calling CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW',l_mod_name,2);
7429: CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW
7430: (px_PRODUCT_TRANSACTION_ID => l_prod_txn_id,
7431: p_REPAIR_LINE_ID => l_rep_line_id,
7432: p_ESTIMATE_DETAIL_ID => NULL,
7425: RAISE PROCESS_ERROR;
7426: End;
7427: Begin
7428: Debug('Calling CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW',l_mod_name,2);
7429: CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW
7430: (px_PRODUCT_TRANSACTION_ID => l_prod_txn_id,
7431: p_REPAIR_LINE_ID => l_rep_line_id,
7432: p_ESTIMATE_DETAIL_ID => NULL,
7433: p_ACTION_TYPE => l_action_type,
7503: Select product_transaction_id,
7504: repair_line_id
7505: into l_prod_txn_id,
7506: l_rep_line_id
7507: from csd_product_transactions
7508: where order_header_id = DEL.header_id
7509: and order_line_id = DEL.line_id
7510: and action_type = l_action_type
7511: and action_code = l_action_code
7524: l_qty_shipped := 0;
7525: END IF;
7526:
7527: IF l_prod_txn_exists THEN
7528: UPDATE CSD_PRODUCT_TRANSACTIONS
7529: SET quantity_shipped = l_qty_shipped,
7530: release_sales_order_flag = l_release_so_flag,
7531: ship_sales_order_flag = l_ship_so_flag,
7532: prod_txn_status = l_prod_txn_status,
7555: END IF;
7556: RAISE PROCESS_ERROR;
7557: END IF;
7558: ELSE
7559: UPDATE CSD_PRODUCT_TRANSACTIONS
7560: SET quantity_shipped = l_qty_shipped,
7561: order_line_id = DEL.line_id,
7562: release_sales_order_flag = l_release_so_flag,
7563: ship_sales_order_flag = l_ship_so_flag,
7669: Select product_transaction_id,
7670: repair_line_id
7671: into l_prod_txn_id,
7672: l_rep_line_id
7673: from csd_product_transactions
7674: where order_header_id = DEL.header_id
7675: and order_line_id = DEL.line_id
7676: and action_type = l_action_type
7677: and action_code = l_action_code;
7687: Debug('Item Is Non-Serialized ',l_mod_name,1);
7688:
7689: IF l_prod_txn_exists THEN
7690: --If product txn exist then update the shipped qty and the status
7691: UPDATE CSD_PRODUCT_TRANSACTIONS
7692: SET quantity_shipped = nvl(quantity_shipped,0) + nvl(DEL.shipped_quantity,0),
7693: sub_inventory = DEL.subinventory,
7694: lot_number = DEL.lot_number,
7695: locator_id = DEL.locator_id,
7720: -- Get the repair line id for the order header id
7721: Begin
7722: Select repair_line_id
7723: into l_rep_line_id
7724: from csd_product_transactions
7725: where order_header_id = DEL.header_id
7726: and action_type = l_action_type
7727: and action_code = l_action_code
7728: and rownum = 1;
7739: END IF;
7740: RAISE PROCESS_ERROR;
7741: End;
7742: Begin
7743: Debug('Calling CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW',l_mod_name,2);
7744: CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW
7745: (px_PRODUCT_TRANSACTION_ID => l_prod_txn_id,
7746: p_REPAIR_LINE_ID => l_rep_line_id,
7747: p_ESTIMATE_DETAIL_ID => NULL,
7740: RAISE PROCESS_ERROR;
7741: End;
7742: Begin
7743: Debug('Calling CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW',l_mod_name,2);
7744: CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW
7745: (px_PRODUCT_TRANSACTION_ID => l_prod_txn_id,
7746: p_REPAIR_LINE_ID => l_rep_line_id,
7747: p_ESTIMATE_DETAIL_ID => NULL,
7748: p_ACTION_TYPE => l_action_type,
7917: Select cpt.product_transaction_id,
7918: cpt.repair_line_id
7919: into l_prod_txn_id,
7920: l_rep_line_id
7921: from csd_product_transactions cpt,
7922: csd_repairs cra
7923: where cpt.order_header_id = DEL.Header_id
7924: and cpt.action_type = 'MOVE_OUT'
7925: and cpt.action_code = 'USABLES'
7931:
7932: Debug('Product txn line found',l_mod_name,1);
7933:
7934: -- Updating the product txns with status,shipped_qty
7935: Update csd_product_transactions
7936: set prod_txn_status = 'SHIPPED',
7937: quantity_shipped = 1,
7938: sub_inventory = DEL.subinventory,
7939: locator_id = DEL.locator_id,
8027: Select cpt.product_transaction_id,
8028: cpt.repair_line_id
8029: into l_prod_txn_id,
8030: l_rep_line_id
8031: from csd_product_transactions cpt,
8032: csd_repairs cra
8033: where cpt.repair_line_id = cra.repair_line_id
8034: and cpt.order_header_id = DEL.header_id
8035: and cpt.source_serial_number is null
8050: and wdd.source_header_id = DEL.header_id)
8051: and rownum = 1;
8052:
8053: -- Updating the product txns with status,shipped_qty
8054: Update csd_product_transactions
8055: set prod_txn_status = 'SHIPPED',
8056: quantity_shipped = 1,
8057: sub_inventory = DEL.subinventory,
8058: locator_id = DEL.locator_id,
8174: Select cpt.product_transaction_id,
8175: cpt.repair_line_id
8176: into l_prod_txn_id,
8177: l_rep_line_id
8178: from csd_product_transactions cpt,
8179: csd_repairs cra
8180: where cpt.repair_line_id = cra.repair_line_id
8181: and cra.supercession_inv_item_id is null
8182: and cpt.source_serial_number is null
8187:
8188: Debug('Product txn line found',l_mod_name,1);
8189:
8190: -- Updating the product txns with status,shipped_qty
8191: Update csd_product_transactions
8192: set prod_txn_status = 'SHIPPED',
8193: quantity_shipped = 1,
8194: sub_inventory = DEL.subinventory,
8195: locator_id = DEL.locator_id,
8283: Select cpt.product_transaction_id,
8284: cpt.repair_line_id
8285: into l_prod_txn_id,
8286: l_rep_line_id
8287: from csd_product_transactions cpt,
8288: csd_repairs cra
8289: where cpt.repair_line_id = cra.repair_line_id
8290: and cpt.order_header_id = DEL.header_id
8291: and cpt.source_serial_number is null
8306: and wdd.source_header_id = DEL.header_id)
8307: and rownum = 1;
8308:
8309: -- Updating the product txns with status,shipped_qty
8310: Update csd_product_transactions
8311: set prod_txn_status = 'SHIPPED',
8312: quantity_shipped = 1,
8313: sub_inventory = DEL.subinventory,
8314: locator_id = DEL.locator_id,
9357: CURSOR c_product_transaction_id(p_repair_line_id IN number) is
9358: SELECT c.product_transaction_id, a.booked_flag
9359: FROM oe_order_lines_all a,
9360: cs_estimate_details b,
9361: csd_product_transactions c
9362: WHERE a.line_id = b.order_line_id
9363: AND b.estimate_detail_id = c.estimate_detail_id
9364: and c.prod_txn_status = 'SUBMITTED'
9365: and a.booked_flag = 'Y'
9366: and c.book_sales_order_flag = 'N'
9367: and b.order_header_id in
9368: (
9369: select p.order_header_id
9370: from cs_estimate_details p, csd_product_transactions q
9371: where p.estimate_detail_id=q.estimate_detail_id
9372: and q.repair_line_id=p_repair_line_id
9373: );
9374:
9376:
9377: FOR C in c_product_transaction_id(p_repair_line_id)
9378: Loop
9379: IF C.booked_flag = 'Y' THEN
9380: UPDATE csd_product_transactions
9381: SET prod_txn_status = 'BOOKED', book_sales_order_flag = 'Y'
9382: WHERE product_transaction_id = C.product_transaction_id;
9383: END IF;
9384: end loop;
9418: SELECT DISTINCT cpt.product_transaction_id PRODUCT_TXN_ID
9419: FROM oe_order_headers_all oeh,
9420: oe_order_lines_all oel,
9421: cs_estimate_details ced,
9422: csd_product_transactions cpt
9423: WHERE cpt.repair_line_id = p_repair_line_id
9424: AND cpt.action_type in ('RMA', 'SHIP', 'RMA_THIRD_PTY', 'SHIP_THIRD_PTY')
9425: AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
9426: AND ced.order_header_id is not null
9454:
9455: FOR ORD_LINES IN CANCELLED_ORDER_LINES( p_repair_line_id) LOOP
9456: l_product_txn_id := ORD_LINES.PRODUCT_TXN_ID;
9457: BEGIN
9458: UPDATE CSD_PRODUCT_TRANSACTIONS
9459: SET PROD_TXN_STATUS = C_PRODTXN_CANCELLED,
9460: LAST_UPDATE_DATE = SYSDATE,
9461: LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
9462: LAST_UPDATED_BY = FND_GLOBAL.USER_ID