[Home] [Help]
36: ro_serial_number csd_repairs.serial_number%type ,
37: repair_number csd_repairs.repair_number%type,
38: ro_uom csd_repairs.unit_of_measure%type,
39: ro_item_id csd_repairs.inventory_item_id%type ,
40: product_transaction_id csd_product_transactions.product_transaction_id%type,
41: repair_line_id csd_product_transactions.repair_line_id%type,
42: action_code csd_product_transactions.action_code%type,
43: action_type csd_product_transactions.action_type%type, --bug#10099900
44: source_serial_number csd_product_transactions.source_serial_number%type,
37: repair_number csd_repairs.repair_number%type,
38: ro_uom csd_repairs.unit_of_measure%type,
39: ro_item_id csd_repairs.inventory_item_id%type ,
40: product_transaction_id csd_product_transactions.product_transaction_id%type,
41: repair_line_id csd_product_transactions.repair_line_id%type,
42: action_code csd_product_transactions.action_code%type,
43: action_type csd_product_transactions.action_type%type, --bug#10099900
44: source_serial_number csd_product_transactions.source_serial_number%type,
45: source_instance_id csd_product_transactions.source_instance_id%type,
38: ro_uom csd_repairs.unit_of_measure%type,
39: ro_item_id csd_repairs.inventory_item_id%type ,
40: product_transaction_id csd_product_transactions.product_transaction_id%type,
41: repair_line_id csd_product_transactions.repair_line_id%type,
42: action_code csd_product_transactions.action_code%type,
43: action_type csd_product_transactions.action_type%type, --bug#10099900
44: source_serial_number csd_product_transactions.source_serial_number%type,
45: source_instance_id csd_product_transactions.source_instance_id%type,
46: prod_txn_recd_qty csd_product_transactions.quantity_received%type,
39: ro_item_id csd_repairs.inventory_item_id%type ,
40: product_transaction_id csd_product_transactions.product_transaction_id%type,
41: repair_line_id csd_product_transactions.repair_line_id%type,
42: action_code csd_product_transactions.action_code%type,
43: action_type csd_product_transactions.action_type%type, --bug#10099900
44: source_serial_number csd_product_transactions.source_serial_number%type,
45: source_instance_id csd_product_transactions.source_instance_id%type,
46: prod_txn_recd_qty csd_product_transactions.quantity_received%type,
47: estimate_quantity cs_estimate_details.quantity_required%type ,
40: product_transaction_id csd_product_transactions.product_transaction_id%type,
41: repair_line_id csd_product_transactions.repair_line_id%type,
42: action_code csd_product_transactions.action_code%type,
43: action_type csd_product_transactions.action_type%type, --bug#10099900
44: source_serial_number csd_product_transactions.source_serial_number%type,
45: source_instance_id csd_product_transactions.source_instance_id%type,
46: prod_txn_recd_qty csd_product_transactions.quantity_received%type,
47: estimate_quantity cs_estimate_details.quantity_required%type ,
48: est_order_line_id cs_estimate_details.order_line_id%type ,
41: repair_line_id csd_product_transactions.repair_line_id%type,
42: action_code csd_product_transactions.action_code%type,
43: action_type csd_product_transactions.action_type%type, --bug#10099900
44: source_serial_number csd_product_transactions.source_serial_number%type,
45: source_instance_id csd_product_transactions.source_instance_id%type,
46: prod_txn_recd_qty csd_product_transactions.quantity_received%type,
47: estimate_quantity cs_estimate_details.quantity_required%type ,
48: est_order_line_id cs_estimate_details.order_line_id%type ,
49: prod_txn_item_id cs_estimate_details.inventory_item_id%type
42: action_code csd_product_transactions.action_code%type,
43: action_type csd_product_transactions.action_type%type, --bug#10099900
44: source_serial_number csd_product_transactions.source_serial_number%type,
45: source_instance_id csd_product_transactions.source_instance_id%type,
46: prod_txn_recd_qty csd_product_transactions.quantity_received%type,
47: estimate_quantity cs_estimate_details.quantity_required%type ,
48: est_order_line_id cs_estimate_details.order_line_id%type ,
49: prod_txn_item_id cs_estimate_details.inventory_item_id%type
50: );
50: );
51:
52: TYPE IO_RCPT_LINES_Rec_Type IS RECORD
53: (
54: product_transaction_id csd_product_transactions.PRODUCT_TRANSACTION_ID%type,
55: prod_txn_status csd_product_transactions.PROD_TXN_STATUS%type,
56: repair_line_id csd_product_transactions.REPAIR_LINE_ID%type,
57: order_header_id csd_product_transactions.ORDER_HEADER_ID%type,
58: order_line_id csd_product_transactions.ORDER_LINE_ID%type,
51:
52: TYPE IO_RCPT_LINES_Rec_Type IS RECORD
53: (
54: product_transaction_id csd_product_transactions.PRODUCT_TRANSACTION_ID%type,
55: prod_txn_status csd_product_transactions.PROD_TXN_STATUS%type,
56: repair_line_id csd_product_transactions.REPAIR_LINE_ID%type,
57: order_header_id csd_product_transactions.ORDER_HEADER_ID%type,
58: order_line_id csd_product_transactions.ORDER_LINE_ID%type,
59: req_header_id csd_product_transactions.REQ_HEADER_ID%type,
52: TYPE IO_RCPT_LINES_Rec_Type IS RECORD
53: (
54: product_transaction_id csd_product_transactions.PRODUCT_TRANSACTION_ID%type,
55: prod_txn_status csd_product_transactions.PROD_TXN_STATUS%type,
56: repair_line_id csd_product_transactions.REPAIR_LINE_ID%type,
57: order_header_id csd_product_transactions.ORDER_HEADER_ID%type,
58: order_line_id csd_product_transactions.ORDER_LINE_ID%type,
59: req_header_id csd_product_transactions.REQ_HEADER_ID%type,
60: req_line_id csd_product_transactions.REPAIR_LINE_ID%type,
53: (
54: product_transaction_id csd_product_transactions.PRODUCT_TRANSACTION_ID%type,
55: prod_txn_status csd_product_transactions.PROD_TXN_STATUS%type,
56: repair_line_id csd_product_transactions.REPAIR_LINE_ID%type,
57: order_header_id csd_product_transactions.ORDER_HEADER_ID%type,
58: order_line_id csd_product_transactions.ORDER_LINE_ID%type,
59: req_header_id csd_product_transactions.REQ_HEADER_ID%type,
60: req_line_id csd_product_transactions.REPAIR_LINE_ID%type,
61: prod_txn_rcvd_qty csd_product_transactions.quantity_received%type,
54: product_transaction_id csd_product_transactions.PRODUCT_TRANSACTION_ID%type,
55: prod_txn_status csd_product_transactions.PROD_TXN_STATUS%type,
56: repair_line_id csd_product_transactions.REPAIR_LINE_ID%type,
57: order_header_id csd_product_transactions.ORDER_HEADER_ID%type,
58: order_line_id csd_product_transactions.ORDER_LINE_ID%type,
59: req_header_id csd_product_transactions.REQ_HEADER_ID%type,
60: req_line_id csd_product_transactions.REPAIR_LINE_ID%type,
61: prod_txn_rcvd_qty csd_product_transactions.quantity_received%type,
62: ro_qty csd_repairs.quantity%type,
55: prod_txn_status csd_product_transactions.PROD_TXN_STATUS%type,
56: repair_line_id csd_product_transactions.REPAIR_LINE_ID%type,
57: order_header_id csd_product_transactions.ORDER_HEADER_ID%type,
58: order_line_id csd_product_transactions.ORDER_LINE_ID%type,
59: req_header_id csd_product_transactions.REQ_HEADER_ID%type,
60: req_line_id csd_product_transactions.REPAIR_LINE_ID%type,
61: prod_txn_rcvd_qty csd_product_transactions.quantity_received%type,
62: ro_qty csd_repairs.quantity%type,
63: ro_rcvd_qty csd_repairs.quantity_rcvd%type,
56: repair_line_id csd_product_transactions.REPAIR_LINE_ID%type,
57: order_header_id csd_product_transactions.ORDER_HEADER_ID%type,
58: order_line_id csd_product_transactions.ORDER_LINE_ID%type,
59: req_header_id csd_product_transactions.REQ_HEADER_ID%type,
60: req_line_id csd_product_transactions.REPAIR_LINE_ID%type,
61: prod_txn_rcvd_qty csd_product_transactions.quantity_received%type,
62: ro_qty csd_repairs.quantity%type,
63: ro_rcvd_qty csd_repairs.quantity_rcvd%type,
64: inventory_item_id csd_repairs.inventory_item_id%type,
57: order_header_id csd_product_transactions.ORDER_HEADER_ID%type,
58: order_line_id csd_product_transactions.ORDER_LINE_ID%type,
59: req_header_id csd_product_transactions.REQ_HEADER_ID%type,
60: req_line_id csd_product_transactions.REPAIR_LINE_ID%type,
61: prod_txn_rcvd_qty csd_product_transactions.quantity_received%type,
62: ro_qty csd_repairs.quantity%type,
63: ro_rcvd_qty csd_repairs.quantity_rcvd%type,
64: inventory_item_id csd_repairs.inventory_item_id%type,
65: ro_uom csd_repairs.unit_of_measure%type,
91: repair_line_id csd_repairs.repair_line_id%type,
92: ro_uom csd_repairs.unit_of_measure%type ,
93: ro_item_id csd_repairs.inventory_item_id%type ,
94: estimate_quantity cs_estimate_details.quantity_required%type ,
95: prod_txn_serial_num csd_product_transactions.source_serial_number%type ,
96: source_instance_id csd_product_transactions.source_instance_id%type,
97: product_transaction_id csd_product_transactions.product_transaction_id%type,
98: action_code csd_product_transactions.action_code%type,
99: delivery_name wsh_new_deliveries.name%type ,
92: ro_uom csd_repairs.unit_of_measure%type ,
93: ro_item_id csd_repairs.inventory_item_id%type ,
94: estimate_quantity cs_estimate_details.quantity_required%type ,
95: prod_txn_serial_num csd_product_transactions.source_serial_number%type ,
96: source_instance_id csd_product_transactions.source_instance_id%type,
97: product_transaction_id csd_product_transactions.product_transaction_id%type,
98: action_code csd_product_transactions.action_code%type,
99: delivery_name wsh_new_deliveries.name%type ,
100: org_name hr_all_organization_units.name%type
93: ro_item_id csd_repairs.inventory_item_id%type ,
94: estimate_quantity cs_estimate_details.quantity_required%type ,
95: prod_txn_serial_num csd_product_transactions.source_serial_number%type ,
96: source_instance_id csd_product_transactions.source_instance_id%type,
97: product_transaction_id csd_product_transactions.product_transaction_id%type,
98: action_code csd_product_transactions.action_code%type,
99: delivery_name wsh_new_deliveries.name%type ,
100: org_name hr_all_organization_units.name%type
101: );
94: estimate_quantity cs_estimate_details.quantity_required%type ,
95: prod_txn_serial_num csd_product_transactions.source_serial_number%type ,
96: source_instance_id csd_product_transactions.source_instance_id%type,
97: product_transaction_id csd_product_transactions.product_transaction_id%type,
98: action_code csd_product_transactions.action_code%type,
99: delivery_name wsh_new_deliveries.name%type ,
100: org_name hr_all_organization_units.name%type
101: );
102: TYPE IO_SHIP_LINES_Rec_Type IS RECORD
2143: l_srl_ctl_code mtl_system_items.serial_number_control_code%type;
2144: l_lot_ctl_code mtl_system_items.lot_control_code%type;
2145: l_ib_flag mtl_system_items.comms_nl_trackable_flag%type;
2146: l_instance_id csi_item_instances.instance_id%type;
2147: l_prod_txn_status csd_product_transactions.prod_txn_status%type;
2148:
2149: -- activity record
2150: l_activity_rec activity_rec_type;
2151:
2200: cpt.quantity_received prod_txn_recd_qty,
2201: abs(ced.quantity_required) estimate_quantity,
2202: ced.order_line_id est_order_line_id,
2203: ced.inventory_item_id prod_txn_item_id
2204: FROM csd_product_transactions cpt,
2205: cs_estimate_details ced,
2206: csd_repairs cra,
2207: rcv_transactions rcvt,
2208: oe_order_headers_all oeh,
2278: cpt.quantity_received prod_txn_recd_qty,
2279: abs(ced.quantity_required) estimate_quantity,
2280: ced.order_line_id est_order_line_id,
2281: ced.inventory_item_id prod_txn_item_id
2282: FROM csd_product_transactions cpt,
2283: cs_estimate_details ced,
2284: csd_repairs cra,
2285: rcv_transactions rcvt,
2286: oe_order_headers_all oeh,
2357: oe_order_headers_all oeh,
2358: oe_order_lines_all oel,
2359: rcv_transactions rcvt,
2360: cs_estimate_details ced,
2361: csd_product_transactions cpt
2362: WHERE cpt.repair_line_id = p_repair_line_id
2363: AND cpt.action_type in ('RMA', 'RMA_THIRD_PTY') -- excluded walk-in-receipt as it is going off
2364: AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
2365: AND ced.order_header_id is not null
2402: CURSOR Cur_Cancelled_repair_lines IS
2403: SELECT cra.REPAIR_LINE_ID
2404: FROM csd_repairs cra,
2405: cs_estimate_details ced,
2406: csd_product_transactions cpt
2407: WHERE cpt.action_type in ('RMA', 'RMA_THIRD_PTY')
2408: AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
2409: AND ced.order_header_id is not null
2410: AND ced.source_code = 'DR'
2851: WHEN TOO_MANY_ROWS THEN
2852: -- that means multiple lotnumbers were entered during receipt (repair_quantity > 1)
2853: -- bug 160177759
2854: --l_lot_number := '
2855: l_lot_number := NULL; --we will update null value to column: lot number on the csd_product_transactions
2856: --bug#16017759
2857: END;
2858: ELSE
2859: l_lot_number := NULL;
2951: l_prod_txn_status := 'BOOKED';
2952: END IF;
2953:
2954: -- Update the quantity received,locator id, lot number
2955: Update csd_product_transactions
2956: set sub_inventory = I.subinventory,
2957: locator_id = I.locator_id,
2958: lot_number_rcvd = l_lot_number,
2959: source_instance_id = l_instance_id,
2987: --bug#13505961
2988: --need to update the received organization_id
2989: UPDATE CS_ESTIMATE_DETAILS
2990: set transaction_inventory_org = i.organization_id
2991: where estimate_detail_id = (select estimate_detail_id from CSD_PRODUCT_TRANSACTIONS
2992: where product_transaction_id = i.product_transaction_id);
2993:
2994: --bug#13505961
2995:
3252: l_serialized_flag boolean;
3253: l_ord_remaining_qty number := 0;
3254: l_prod_txn_exists boolean;
3255: l_prod_txn_id number := NULL;
3256: l_prod_txn_status csd_product_transactions.prod_txn_status%type;
3257: l_total_qty number;
3258: l_total_del_qty number;
3259: l_total_accept_qty number;
3260: l_total_reject_qty number;
3300: cra.unit_of_measure ro_uom,
3301: prh.segment1 requisition_number,
3302: oel.ordered_quantity,
3303: oeh.order_number
3304: from csd_product_transactions cpt,
3305: csd_repairs cra,
3306: po_requisition_headers_all prh,
3307: oe_order_lines_all oel,
3308: oe_order_headers_all oeh
3336: cra.unit_of_measure ro_uom,
3337: prh.segment1 requisition_number,
3338: oel.ordered_quantity,
3339: oeh.order_number
3340: from csd_product_transactions cpt,
3341: csd_repairs cra,
3342: po_requisition_headers_all prh,
3343: oe_order_lines_all oel,
3344: oe_order_headers_all oeh
3409: CURSOR ORDER_INFO (p_ord_header_id in number) IS
3410: Select distinct
3411: order_header_id,
3412: order_line_id
3413: from csd_product_transactions
3414: where order_header_id = p_ord_header_id
3415: AND action_type = 'MOVE_IN'
3416: AND action_code = 'DEFECTIVES'
3417: AND prod_txn_status = 'SHIPPED';
3444: -- Api body starts
3445:
3446: -- In case of Internal orders, the product txns are stamped
3447: -- with the order header id and line id.
3448: -- So Validate if it exists in csd_product_transactions
3449: IF NVL(p_order_header_id,-999) <> -999 THEN
3450: BEGIN
3451: select 'EXISTS'
3452: into l_dummy
3454: po_requisition_headers_all prh
3455: where oeh.source_document_id = prh.requisition_header_id
3456: and oeh.header_id = p_order_header_id
3457: and exists (select 'x'
3458: from csd_product_transactions cpt
3459: where cpt.action_type = 'MOVE_IN'
3460: and cpt.action_code = 'DEFECTIVES'
3461: and cpt.order_header_id = oeh.header_id);
3462: EXCEPTION
3682: where rcvt.transaction_id = DEL.transaction_id
3683: and rownum = 1
3684: and not exists (Select 'NOT EXIST'
3685: from csd_repairs cra,
3686: csd_product_transactions cpt
3687: where cra.repair_line_id = cpt.repair_line_id
3688: and cpt.action_type = 'MOVE_IN'
3689: and cpt.order_header_id = ro.order_header_id
3690: and cra.serial_number = rcvt.serial_num);
3816: -- Updating the product txn with the serial number,lot number
3817: -- qty rcvd and subinventory
3818: -- sub_inventory_rcvd is used for IO and subinventory column
3819: -- is used for the regular RMA
3820: UPDATE CSD_PRODUCT_TRANSACTIONS
3821: SET SOURCE_SERIAL_NUMBER = l_serial_num,
3822: source_instance_id = l_instance_id,
3823: LOT_NUMBER_RCVD = l_lot_num,
3824: LOCATOR_ID = DEL.locator_id,
3842: RAISE PROCESS_ERROR;
3843: END IF;
3844:
3845: IF RCV.serial_number_control_code in (2,5,6) THEN
3846: UPDATE CSD_PRODUCT_TRANSACTIONS
3847: SET prod_txn_status = 'RECEIVED',
3848: object_version_number = object_version_number+1,
3849: last_update_date = sysdate,
3850: last_updated_by = fnd_global.user_id,
3850: last_updated_by = fnd_global.user_id,
3851: last_update_login = fnd_global.login_id
3852: WHERE product_transaction_id = RO.product_transaction_id;
3853: ELSE
3854: UPDATE CSD_PRODUCT_TRANSACTIONS
3855: SET prod_txn_status = 'RECEIVED',
3856: object_version_number = object_version_number+1,
3857: last_update_date = sysdate,
3858: last_updated_by = fnd_global.user_id,
4050: where rcvt.transaction_id = DEL.transaction_id
4051: and rownum = 1
4052: and not exists (Select 'NOT EXIST'
4053: from csd_repairs cra,
4054: csd_product_transactions cpt
4055: where cra.repair_line_id = cpt.repair_line_id
4056: and cpt.action_type = 'MOVE_IN'
4057: and cpt.order_header_id = ro.order_header_id
4058: and cra.serial_number = rcvt.serial_num);
4185: -- Updating the product txn with the serial number,lot number
4186: -- qty rcvd and subinventory
4187: -- sub_inventory_rcvd is used for IO and subinventory column
4188: -- is used for the regular RMA
4189: UPDATE CSD_PRODUCT_TRANSACTIONS
4190: SET SOURCE_SERIAL_NUMBER = l_serial_num,
4191: source_instance_id = l_instance_id,
4192: LOT_NUMBER_RCVD = l_lot_num,
4193: LOCATOR_ID = DEL.locator_id,
4211: RAISE PROCESS_ERROR;
4212: END IF;
4213:
4214: IF RCV.serial_number_control_code in (2,5,6) THEN
4215: UPDATE CSD_PRODUCT_TRANSACTIONS
4216: SET prod_txn_status = 'RECEIVED',
4217: object_version_number = object_version_number+1,
4218: last_update_date = sysdate,
4219: last_updated_by = fnd_global.user_id,
4220: last_update_login = fnd_global.login_id
4221: WHERE product_transaction_id = RO.product_transaction_id;
4222:
4223: ELSE
4224: UPDATE CSD_PRODUCT_TRANSACTIONS
4225: SET prod_txn_status = 'RECEIVED',
4226: object_version_number = object_version_number+1,
4227: last_update_date = sysdate,
4228: last_updated_by = fnd_global.user_id,
4327: where rcvt.transaction_id = RCV.transaction_id
4328: and rownum = 1
4329: and not exists (Select 'NOT EXIST'
4330: from csd_repairs cra,
4331: csd_product_transactions cpt
4332: where cra.repair_line_id = cpt.repair_line_id
4333: and cpt.order_header_id = ro.order_header_id
4334: and cra.serial_number = rcvt.serial_num);
4335: Exception
4458:
4459:
4460: -- Update product txn with the rcvd serial number,lot number
4461: -- locator id, sub inv, status and the rcvd qty
4462: UPDATE CSD_PRODUCT_TRANSACTIONS
4463: SET SOURCE_SERIAL_NUMBER = l_serial_num,
4464: source_instance_id = l_instance_id,
4465: LOT_NUMBER_RCVD = l_lot_num,
4466: QUANTITY_RECEIVED = NVL(QUANTITY_RECEIVED,0) + l_rcvd_qty,
4484: RAISE PROCESS_ERROR;
4485: END IF;
4486:
4487: IF RCV.serial_number_control_code in (2,5,6) THEN
4488: UPDATE CSD_PRODUCT_TRANSACTIONS
4489: SET prod_txn_status = 'RECEIVED',
4490: object_version_number = object_version_number+1,
4491: last_update_date = sysdate,
4492: last_updated_by = fnd_global.user_id,
4492: last_updated_by = fnd_global.user_id,
4493: last_update_login = fnd_global.login_id
4494: WHERE product_transaction_id = RO.product_transaction_id;
4495: ELSE
4496: UPDATE CSD_PRODUCT_TRANSACTIONS
4497: SET prod_txn_status = 'RECEIVED',
4498: object_version_number = object_version_number+1,
4499: last_update_date = sysdate,
4500: last_updated_by = fnd_global.user_id,
4666: l_serialized_flag boolean;
4667: l_ord_remaining_qty number := 0;
4668: l_prod_txn_exists boolean;
4669: l_prod_txn_id number := NULL;
4670: l_prod_txn_status csd_product_transactions.prod_txn_status%type;
4671: l_total_qty number;
4672: l_total_del_qty number;
4673: l_total_accept_qty number;
4674: l_total_reject_qty number;
4714: cra.unit_of_measure ro_uom,
4715: prh.segment1 requisition_number,
4716: oel.ordered_quantity,
4717: oeh.order_number
4718: from csd_product_transactions cpt,
4719: csd_repairs cra,
4720: po_requisition_headers_all prh,
4721: oe_order_lines_all oel,
4722: oe_order_headers_all oeh
4750: cra.unit_of_measure ro_uom,
4751: prh.segment1 requisition_number,
4752: oel.ordered_quantity,
4753: oeh.order_number
4754: from csd_product_transactions cpt,
4755: csd_repairs cra,
4756: po_requisition_headers_all prh,
4757: oe_order_lines_all oel,
4758: oe_order_headers_all oeh
4825: CURSOR ORDER_INFO (p_ord_header_id in number) IS
4826: Select distinct
4827: order_header_id,
4828: order_line_id
4829: from csd_product_transactions
4830: where order_header_id = p_ord_header_id
4831: AND action_type = 'MOVE_OUT'
4832: AND action_code = 'USABLES'
4833: AND prod_txn_status = 'SHIPPED';
4860: -- Api body starts
4861:
4862: -- In case of Internal orders, the product txns are stamped
4863: -- with the order header id and line id.
4864: -- So Validate if it exists in csd_product_transactions
4865: IF NVL(p_order_header_id,-999) <> -999 THEN
4866: BEGIN
4867: select 'EXISTS'
4868: into l_dummy
4870: po_requisition_headers_all prh
4871: where oeh.source_document_id = prh.requisition_header_id
4872: and oeh.header_id = p_order_header_id
4873: and exists (select 'x'
4874: from csd_product_transactions cpt
4875: where cpt.action_type = 'MOVE_OUT'
4876: and cpt.action_code = 'USABLES'
4877: and cpt.order_header_id = oeh.header_id);
4878: EXCEPTION
5098: where rcvt.transaction_id = DEL.transaction_id
5099: and rownum = 1
5100: and not exists (Select 'NOT EXIST'
5101: from csd_repairs cra,
5102: csd_product_transactions cpt
5103: where cra.repair_line_id = cpt.repair_line_id
5104: and cpt.action_type = 'MOVE_OUT'
5105: and cpt.order_header_id = ro.order_header_id
5106: and cra.serial_number = rcvt.serial_num);
5237: -- Updating the product txn with the serial number,lot number
5238: -- qty rcvd and subinventory
5239: -- sub_inventory_rcvd is used for IO and subinventory column
5240: -- is used for the regular RMA
5241: UPDATE CSD_PRODUCT_TRANSACTIONS
5242: SET SOURCE_SERIAL_NUMBER = l_serial_num,
5243: source_instance_id = l_instance_id,
5244: LOT_NUMBER_RCVD = l_lot_num,
5245: LOCATOR_ID = DEL.locator_id,
5263: RAISE PROCESS_ERROR;
5264: END IF;
5265:
5266: IF RCV.serial_number_control_code in (2,5,6) THEN
5267: UPDATE CSD_PRODUCT_TRANSACTIONS
5268: SET prod_txn_status = 'RECEIVED',
5269: object_version_number = object_version_number+1,
5270: last_update_date = sysdate,
5271: last_updated_by = fnd_global.user_id,
5271: last_updated_by = fnd_global.user_id,
5272: last_update_login = fnd_global.login_id
5273: WHERE product_transaction_id = RO.product_transaction_id;
5274: ELSE
5275: UPDATE CSD_PRODUCT_TRANSACTIONS
5276: SET prod_txn_status = 'RECEIVED',
5277: object_version_number = object_version_number+1,
5278: last_update_date = sysdate,
5279: last_updated_by = fnd_global.user_id,
5471: where rcvt.transaction_id = DEL.transaction_id
5472: and rownum = 1
5473: and not exists (Select 'NOT EXIST'
5474: from csd_repairs cra,
5475: csd_product_transactions cpt
5476: where cra.repair_line_id = cpt.repair_line_id
5477: and cpt.action_type = 'MOVE_OUT'
5478: and cpt.order_header_id = ro.order_header_id
5479: and cra.serial_number = rcvt.serial_num);
5611: -- Updating the product txn with the serial number,lot number
5612: -- qty rcvd and subinventory
5613: -- sub_inventory_rcvd is used for IO and subinventory column
5614: -- is used for the regular RMA
5615: UPDATE CSD_PRODUCT_TRANSACTIONS
5616: SET SOURCE_SERIAL_NUMBER = l_serial_num,
5617: source_instance_id = l_instance_id,
5618: LOT_NUMBER_RCVD = l_lot_num,
5619: LOCATOR_ID = DEL.locator_id,
5637: RAISE PROCESS_ERROR;
5638: END IF;
5639:
5640: IF RCV.serial_number_control_code in (2,5,6) THEN
5641: UPDATE CSD_PRODUCT_TRANSACTIONS
5642: SET prod_txn_status = 'RECEIVED',
5643: object_version_number = object_version_number+1,
5644: last_update_date = sysdate,
5645: last_updated_by = fnd_global.user_id,
5646: last_update_login = fnd_global.login_id
5647: WHERE product_transaction_id = RO.product_transaction_id;
5648:
5649: ELSE
5650: UPDATE CSD_PRODUCT_TRANSACTIONS
5651: SET prod_txn_status = 'RECEIVED',
5652: object_version_number = object_version_number+1,
5653: last_update_date = sysdate,
5654: last_updated_by = fnd_global.user_id,
5761: --the serial number in the production transaction table. After commented this
5762: --condition, it worked for the customer.
5763: -- and not exists (Select 'NOT EXIST'
5764: -- from csd_repairs cra,
5765: -- csd_product_transactions cpt
5766: -- where cra.repair_line_id = cpt.repair_line_id
5767: -- and cpt.order_header_id = ro.order_header_id
5768: -- and cra.serial_number = rcvt.serial_num);
5769: Exception
5897: */
5898:
5899: -- Update product txn with the rcvd serial number,lot number
5900: -- locator id, sub inv, status and the rcvd qty
5901: UPDATE CSD_PRODUCT_TRANSACTIONS
5902: SET SOURCE_SERIAL_NUMBER = l_serial_num,
5903: source_instance_id = l_instance_id,
5904: LOT_NUMBER_RCVD = l_lot_num,
5905: QUANTITY_RECEIVED = NVL(QUANTITY_RECEIVED,0) + l_rcvd_qty,
5923: RAISE PROCESS_ERROR;
5924: END IF;
5925:
5926: IF RCV.serial_number_control_code in (2,5,6) THEN
5927: UPDATE CSD_PRODUCT_TRANSACTIONS
5928: SET prod_txn_status = 'RECEIVED',
5929: object_version_number = object_version_number+1,
5930: last_update_date = sysdate,
5931: last_updated_by = fnd_global.user_id,
5931: last_updated_by = fnd_global.user_id,
5932: last_update_login = fnd_global.login_id
5933: WHERE product_transaction_id = RO.product_transaction_id;
5934: ELSE
5935: UPDATE CSD_PRODUCT_TRANSACTIONS
5936: SET prod_txn_status = 'RECEIVED',
5937: object_version_number = object_version_number+1,
5938: last_update_date = sysdate,
5939: last_updated_by = fnd_global.user_id,
6378: cpt.action_code,
6379: wnd.name delivery_name,
6380: hao.name org_name
6381: from
6382: csd_product_transactions cpt,
6383: cs_estimate_details ced,
6384: csd_repairs cra,
6385: wsh_delivery_details dd ,
6386: wsh_serial_numbers dsn,--Added to fix 3801614
6455: cpt.action_code,
6456: wnd.name delivery_name,
6457: hao.name org_name
6458: from
6459: csd_product_transactions cpt,
6460: cs_estimate_details ced,
6461: csd_repairs cra,
6462: wsh_delivery_details dd ,
6463: wsh_serial_numbers dsn,--Added to fix 3801614
6540: cpt.action_code,
6541: wnd.name delivery_name,
6542: hao.name org_name
6543: from
6544: csd_product_transactions cpt,
6545: cs_estimate_details ced,
6546: csd_repairs cra,
6547: wsh_delivery_details dd ,
6548: wsh_serial_numbers dsn,--Added to fix 3801614
6584: CURSOR Cur_Cancelled_repair_lines IS
6585: SELECT cra.REPAIR_LINE_ID
6586: FROM csd_repairs cra,
6587: cs_estimate_details ced,
6588: csd_product_transactions cpt
6589: WHERE cpt.action_type in ('SHIP', 'SHIP_THIRD_PTY')
6590: AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
6591: AND ced.order_header_id is not null
6592: AND ced.source_code = 'DR'
6773: --bug#14657082 --15859195
6774: If ((I.released_status = 'Y') and (I.date_shipped is null)) then
6775: -- Updating the product txn with the status
6776: -- if the line qty is fully rcvd
6777: update csd_product_transactions
6778: set prod_txn_status = 'RELEASED',
6779: release_sales_order_flag = 'Y',
6780: object_version_number = object_version_number+1,
6781: last_update_date = sysdate,
7007: If NOT(l_skip_record) Then --bug#8261344
7008:
7009: -- Updating the product txn with qty,subinventory,lot number
7010: -- locator id
7011: update csd_product_transactions
7012: set sub_inventory = i.subinv,
7013: lot_number = i.lot_number,
7014: quantity_shipped = nvl(quantity_shipped,0)+I.shipped_quantity,
7015: locator_id = i.locator_id,
7034: END IF;
7035:
7036: -- Updating the product txn with the status
7037: -- if the line qty is fully rcvd
7038: update csd_product_transactions
7039: set prod_txn_status = 'SHIPPED',
7040: object_version_number = object_version_number+1,
7041: last_update_date = sysdate,
7042: last_updated_by = fnd_global.user_id,
7345: and oel.inventory_item_id= mtl.inventory_item_id
7346: and dd.move_order_line_id = trl.line_id(+) -- Added to fix 4279958
7347: and dd.delivery_detail_id = dsn.delivery_detail_id(+) --Added to fix 3801614
7348: and exists (Select 'x'
7349: from csd_product_transactions cpt
7350: where cpt.order_header_id = oel.header_id
7351: and cpt.prod_txn_status in ('BOOKED','RELEASED'));
7352:
7353: -- New Cursor for all delivery lines.
7401: and oel.inventory_item_id= mtl.inventory_item_id
7402: and dd.move_order_line_id = trl.line_id(+) -- Added to fix 4279958
7403: and dd.delivery_detail_id = dsn.delivery_detail_id(+) --Added to fix 3801614
7404: and exists (Select 'x'
7405: from csd_product_transactions cpt
7406: where cpt.order_header_id = oel.header_id
7407: and cpt.prod_txn_status in ('BOOKED','RELEASED'));
7408:
7409:
7467: l_pt_line_qty number;
7468: l_pt_shipped_qty number;
7469: l_total_shipped_qty number;
7470: l_prod_txn_shipped_qty number;
7471: l_action_type csd_product_transactions.action_type%type;
7472: l_action_code csd_product_transactions.action_code%type;
7473: l_prod_txn_id number := NULL;
7474: l_prod_txn_status csd_product_transactions.prod_txn_status%type ;
7475: l_release_so_flag csd_product_transactions.release_sales_order_flag%type ;
7468: l_pt_shipped_qty number;
7469: l_total_shipped_qty number;
7470: l_prod_txn_shipped_qty number;
7471: l_action_type csd_product_transactions.action_type%type;
7472: l_action_code csd_product_transactions.action_code%type;
7473: l_prod_txn_id number := NULL;
7474: l_prod_txn_status csd_product_transactions.prod_txn_status%type ;
7475: l_release_so_flag csd_product_transactions.release_sales_order_flag%type ;
7476: l_ship_so_flag csd_product_transactions.ship_sales_order_flag%type ;
7470: l_prod_txn_shipped_qty number;
7471: l_action_type csd_product_transactions.action_type%type;
7472: l_action_code csd_product_transactions.action_code%type;
7473: l_prod_txn_id number := NULL;
7474: l_prod_txn_status csd_product_transactions.prod_txn_status%type ;
7475: l_release_so_flag csd_product_transactions.release_sales_order_flag%type ;
7476: l_ship_so_flag csd_product_transactions.ship_sales_order_flag%type ;
7477: l_qty_shipped number;
7478:
7471: l_action_type csd_product_transactions.action_type%type;
7472: l_action_code csd_product_transactions.action_code%type;
7473: l_prod_txn_id number := NULL;
7474: l_prod_txn_status csd_product_transactions.prod_txn_status%type ;
7475: l_release_so_flag csd_product_transactions.release_sales_order_flag%type ;
7476: l_ship_so_flag csd_product_transactions.ship_sales_order_flag%type ;
7477: l_qty_shipped number;
7478:
7479: -- activity record
7472: l_action_code csd_product_transactions.action_code%type;
7473: l_prod_txn_id number := NULL;
7474: l_prod_txn_status csd_product_transactions.prod_txn_status%type ;
7475: l_release_so_flag csd_product_transactions.release_sales_order_flag%type ;
7476: l_ship_so_flag csd_product_transactions.ship_sales_order_flag%type ;
7477: l_qty_shipped number;
7478:
7479: -- activity record
7480: l_activity_rec activity_rec_type;
7515: -- Api body starts
7516:
7517: -- In case of Internal orders, the product txns are stamped
7518: -- with the order header id and line id.
7519: -- So Validate if it exists in csd_product_transactions
7520: IF NVL(p_order_header_id,-999) <> -999 THEN
7521: BEGIN
7522: select 'EXISTS'
7523: into l_dummy
7525: po_requisition_headers_all prh
7526: where oeh.source_document_id = prh.requisition_header_id
7527: and oeh.header_id = p_order_header_id
7528: and exists (select 'x'
7529: from csd_product_transactions cpt
7530: where cpt.order_header_id = oeh.header_id );
7531: EXCEPTION
7532: WHEN NO_DATA_FOUND THEN
7533: IF ( l_error_level >= G_debug_level) THEN
7573: select action_type,
7574: action_code
7575: into l_action_type,
7576: l_action_code
7577: from csd_product_transactions
7578: where order_header_id = DEL.header_id
7579: and rownum = 1;
7580: EXCEPTION
7581: WHEN NO_DATA_FOUND THEN
7616: Debug('Processing the move-in lines ',l_mod_name,1);
7617:
7618: Select nvl(sum(quantity_shipped),0)
7619: into l_total_shipped_qty
7620: from csd_product_transactions
7621: where action_type = 'MOVE_IN'
7622: and action_code = 'DEFECTIVES'
7623: and order_line_id = DEL.line_id
7624: and order_header_id = DEL.header_id;
7649: Select product_transaction_id,
7650: repair_line_id
7651: into l_prod_txn_id,
7652: l_rep_line_id
7653: from csd_product_transactions
7654: where order_header_id = DEL.header_id
7655: and order_line_id = DEL.line_id
7656: and action_type = l_action_type
7657: and action_code = l_action_code;
7664: END;
7665:
7666: IF l_prod_txn_exists THEN
7667: --If product txn exist then update the shipped qty and the status
7668: UPDATE CSD_PRODUCT_TRANSACTIONS
7669: SET quantity_shipped = nvl(quantity_shipped,0) + nvl(DEL.shipped_quantity,0),
7670: sub_inventory = DEL.subinventory,
7671: lot_number = DEL.lot_number,
7672: locator_id = DEL.locator_id,
7697: -- Get the repair line id for the order header id
7698: Begin
7699: Select repair_line_id
7700: into l_rep_line_id
7701: from csd_product_transactions
7702: where order_header_id = DEL.header_id
7703: and action_type = l_action_type
7704: and action_code = l_action_code
7705: and rownum = 1;
7716: END IF;
7717: RAISE PROCESS_ERROR;
7718: End;
7719: Begin
7720: Debug('Calling CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW',l_mod_name,2);
7721: CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW
7722: (px_PRODUCT_TRANSACTION_ID => l_prod_txn_id,
7723: p_REPAIR_LINE_ID => l_rep_line_id,
7724: p_ESTIMATE_DETAIL_ID => NULL,
7717: RAISE PROCESS_ERROR;
7718: End;
7719: Begin
7720: Debug('Calling CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW',l_mod_name,2);
7721: CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW
7722: (px_PRODUCT_TRANSACTION_ID => l_prod_txn_id,
7723: p_REPAIR_LINE_ID => l_rep_line_id,
7724: p_ESTIMATE_DETAIL_ID => NULL,
7725: p_ACTION_TYPE => l_action_type,
7795: Select product_transaction_id,
7796: repair_line_id
7797: into l_prod_txn_id,
7798: l_rep_line_id
7799: from csd_product_transactions
7800: where order_header_id = DEL.header_id
7801: and order_line_id = DEL.line_id
7802: and action_type = l_action_type
7803: and action_code = l_action_code
7816: l_qty_shipped := 0;
7817: END IF;
7818:
7819: IF l_prod_txn_exists THEN
7820: UPDATE CSD_PRODUCT_TRANSACTIONS
7821: SET quantity_shipped = l_qty_shipped,
7822: release_sales_order_flag = l_release_so_flag,
7823: ship_sales_order_flag = l_ship_so_flag,
7824: prod_txn_status = l_prod_txn_status,
7847: END IF;
7848: RAISE PROCESS_ERROR;
7849: END IF;
7850: ELSE
7851: UPDATE CSD_PRODUCT_TRANSACTIONS
7852: SET quantity_shipped = l_qty_shipped,
7853: order_line_id = DEL.line_id,
7854: release_sales_order_flag = l_release_so_flag,
7855: ship_sales_order_flag = l_ship_so_flag,
7961: Select product_transaction_id,
7962: repair_line_id
7963: into l_prod_txn_id,
7964: l_rep_line_id
7965: from csd_product_transactions
7966: where order_header_id = DEL.header_id
7967: and order_line_id = DEL.line_id
7968: and action_type = l_action_type
7969: and action_code = l_action_code;
7979: Debug('Item Is Non-Serialized ',l_mod_name,1);
7980:
7981: IF l_prod_txn_exists THEN
7982: --If product txn exist then update the shipped qty and the status
7983: UPDATE CSD_PRODUCT_TRANSACTIONS
7984: SET quantity_shipped = nvl(quantity_shipped,0) + nvl(DEL.shipped_quantity,0),
7985: sub_inventory = DEL.subinventory,
7986: lot_number = DEL.lot_number,
7987: locator_id = DEL.locator_id,
8012: -- Get the repair line id for the order header id
8013: Begin
8014: Select repair_line_id
8015: into l_rep_line_id
8016: from csd_product_transactions
8017: where order_header_id = DEL.header_id
8018: and action_type = l_action_type
8019: and action_code = l_action_code
8020: and rownum = 1;
8031: END IF;
8032: RAISE PROCESS_ERROR;
8033: End;
8034: Begin
8035: Debug('Calling CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW',l_mod_name,2);
8036: CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW
8037: (px_PRODUCT_TRANSACTION_ID => l_prod_txn_id,
8038: p_REPAIR_LINE_ID => l_rep_line_id,
8039: p_ESTIMATE_DETAIL_ID => NULL,
8032: RAISE PROCESS_ERROR;
8033: End;
8034: Begin
8035: Debug('Calling CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW',l_mod_name,2);
8036: CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW
8037: (px_PRODUCT_TRANSACTION_ID => l_prod_txn_id,
8038: p_REPAIR_LINE_ID => l_rep_line_id,
8039: p_ESTIMATE_DETAIL_ID => NULL,
8040: p_ACTION_TYPE => l_action_type,
8209: Select cpt.product_transaction_id,
8210: cpt.repair_line_id
8211: into l_prod_txn_id,
8212: l_rep_line_id
8213: from csd_product_transactions cpt,
8214: csd_repairs cra
8215: where cpt.order_header_id = DEL.Header_id
8216: and cpt.action_type = 'MOVE_OUT'
8217: and cpt.action_code = 'USABLES'
8223:
8224: Debug('Product txn line found',l_mod_name,1);
8225:
8226: -- Updating the product txns with status,shipped_qty
8227: Update csd_product_transactions
8228: set prod_txn_status = 'SHIPPED',
8229: quantity_shipped = 1,
8230: sub_inventory = DEL.subinventory,
8231: locator_id = DEL.locator_id,
8319: Select cpt.product_transaction_id,
8320: cpt.repair_line_id
8321: into l_prod_txn_id,
8322: l_rep_line_id
8323: from csd_product_transactions cpt,
8324: csd_repairs cra
8325: where cpt.repair_line_id = cra.repair_line_id
8326: and cpt.order_header_id = DEL.header_id
8327: and cpt.source_serial_number is null
8342: and wdd.source_header_id = DEL.header_id)
8343: and rownum = 1;
8344:
8345: -- Updating the product txns with status,shipped_qty
8346: Update csd_product_transactions
8347: set prod_txn_status = 'SHIPPED',
8348: quantity_shipped = 1,
8349: sub_inventory = DEL.subinventory,
8350: locator_id = DEL.locator_id,
8466: Select cpt.product_transaction_id,
8467: cpt.repair_line_id
8468: into l_prod_txn_id,
8469: l_rep_line_id
8470: from csd_product_transactions cpt,
8471: csd_repairs cra
8472: where cpt.repair_line_id = cra.repair_line_id
8473: and cra.supercession_inv_item_id is null
8474: and cpt.source_serial_number is null
8479:
8480: Debug('Product txn line found',l_mod_name,1);
8481:
8482: -- Updating the product txns with status,shipped_qty
8483: Update csd_product_transactions
8484: set prod_txn_status = 'SHIPPED',
8485: quantity_shipped = 1,
8486: sub_inventory = DEL.subinventory,
8487: locator_id = DEL.locator_id,
8575: Select cpt.product_transaction_id,
8576: cpt.repair_line_id
8577: into l_prod_txn_id,
8578: l_rep_line_id
8579: from csd_product_transactions cpt,
8580: csd_repairs cra
8581: where cpt.repair_line_id = cra.repair_line_id
8582: and cpt.order_header_id = DEL.header_id
8583: and cpt.source_serial_number is null
8598: and wdd.source_header_id = DEL.header_id)
8599: and rownum = 1;
8600:
8601: -- Updating the product txns with status,shipped_qty
8602: Update csd_product_transactions
8603: set prod_txn_status = 'SHIPPED',
8604: quantity_shipped = 1,
8605: sub_inventory = DEL.subinventory,
8606: locator_id = DEL.locator_id,
9709: CURSOR c_product_transaction_id(p_repair_line_id IN number) is
9710: SELECT c.product_transaction_id, a.booked_flag
9711: FROM oe_order_lines_all a,
9712: cs_estimate_details b,
9713: csd_product_transactions c
9714: WHERE a.line_id = b.order_line_id
9715: AND b.estimate_detail_id = c.estimate_detail_id
9716: and c.prod_txn_status = 'SUBMITTED'
9717: and a.booked_flag = 'Y'
9718: and c.book_sales_order_flag = 'N'
9719: and b.order_header_id in
9720: (
9721: select p.order_header_id
9722: from cs_estimate_details p, csd_product_transactions q
9723: where p.estimate_detail_id=q.estimate_detail_id
9724: and q.repair_line_id=p_repair_line_id
9725: );
9726:
9728:
9729: FOR C in c_product_transaction_id(p_repair_line_id)
9730: Loop
9731: IF C.booked_flag = 'Y' THEN
9732: UPDATE csd_product_transactions
9733: SET prod_txn_status = 'BOOKED', book_sales_order_flag = 'Y'
9734: WHERE product_transaction_id = C.product_transaction_id;
9735: END IF;
9736: end loop;
9770: SELECT DISTINCT cpt.product_transaction_id PRODUCT_TXN_ID
9771: FROM oe_order_headers_all oeh,
9772: oe_order_lines_all oel,
9773: cs_estimate_details ced,
9774: csd_product_transactions cpt
9775: WHERE cpt.repair_line_id = p_repair_line_id
9776: AND cpt.action_type in ('RMA', 'SHIP', 'RMA_THIRD_PTY', 'SHIP_THIRD_PTY')
9777: AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
9778: AND ced.order_header_id is not null
9806:
9807: FOR ORD_LINES IN CANCELLED_ORDER_LINES( p_repair_line_id) LOOP
9808: l_product_txn_id := ORD_LINES.PRODUCT_TXN_ID;
9809: BEGIN
9810: UPDATE CSD_PRODUCT_TRANSACTIONS
9811: SET PROD_TXN_STATUS = C_PRODTXN_CANCELLED,
9812: LAST_UPDATE_DATE = SYSDATE,
9813: LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
9814: LAST_UPDATED_BY = FND_GLOBAL.USER_ID