WMS Rule With Customer Number As Parameter Are Getting Created Incorrectly In R12 (Doc ID 1567732.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Warehouse Management - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Label Printing

ACTUAL BEHAVIOR
---------------
Label format rule “package” is created incorrectly in R12 when rule is based simply on customer number.

Note: We are upgrading from R11.5.10 to R12.1.3

Created the following label format rule:

Type: Label Format
Name: Label Format for – XXXX
Description: Label Format for – XXXX
Return value: S004450.lwl (the label format)

Weight: 100

Restriction:
Seq: 10
Object: Customers
Parameter: Customer Number
Operator: =
Object: Constant Character
Parameter/value: 004450 (the customer account number)

Compile the rule.

Behind the scenes, WMS creates a package called WMS_RULE_YYYY (where YYYY is the rule_id for the rule).
In this package the following is created:

Line 1: CURSOR get_label_rule_curs IS
Line2: select count(*) from hz_cust_accounts hc
Line 3: ,WMS_LABEL_REQUESTS wlr
Line 4: ,HZ_PARTIES hp where wlr.label_request_id = p_label_request_id and hp.PARTY_ID = wlr.CUSTOMER_ID
Line 5: and hp.PARTY_ID = hc.PARTY_ID
Line 6: and hc.CUST_ACCOUNT_ID = wlr.CUSTOMER_ID
Line 7: and (
Line 8: hp.PARTY_NUMBER = '004450' (the customer account number)
Line 9: )
;

This query is incorrect. Based on TCA, the correct query should be (Corrections on Line 4 and Line 8):

Line 1: CURSOR get_label_rule_curs IS
Line2: select count(*) from hz_cust_accounts hc
Line 3: ,WMS_LABEL_REQUESTS wlr
Line 4: ,HZ_PARTIES hp where wlr.label_request_id = p_label_request_id and hc.CUST_ACCOUNT_ID= wlr.CUSTOMER_ID
Line 5: and hp.PARTY_ID = hc.PARTY_ID
Line 6: and hc.CUST_ACCOUNT_ID = wlr.CUSTOMER_ID
Line 7: and (
Line 8: hc.ACCOUNT_NUMBER = '004450' (the customer account number)
Line 9: )
;


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Create label format rule:

Weight: 100

Restriction:
Seq: 10
Object: Customers
Parameter: Customer Number
Operator: =
Object: Constant Character
Parameter/value: 004450 (the customer account number)

2. Compile the rule.

3. Review the Rule package created.

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot create Label format number with customer number parameter.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms