PL/SQL API to Create Dispute on an Invoice Transaction

Oracle has provided an API IEX_DISPUTE_PUB.CREATE_DISPUTE which is part of Advanced Collections to create a dispute on an Invoice Transaction. Below is the sample API script:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127

SET serveroutput ON;
---
---
 
DECLARE
   lv_return_status_txt    VARCHAR2 (1000);
   lv_msg_count_num        NUMBER;
   lv_msg_count_txt        VARCHAR2 (10000);
   lv_cm_request_id        VARCHAR2 (1000);
   lv_index_num            NUMBER;
   lv_err_msg_stack_txt    VARCHAR2 (4000);
   l_user_id               NUMBER;
   l_responsibility_id     NUMBER := 22941; -- collections agent
   lv_delinquency_id_num   NUMBER;
   l_customer_trx_id       NUMBER := 468415;
   l_line_id_1             NUMBER := 5266682;
   l_line_id_2             NUMBER;
   l_line_id_3             NUMBER;
   l_line_id_4             NUMBER;
   l_org_id                NUMBER;
   l_dispute_lines         BOOLEAN := TRUE;
   disp_header_rec         iex_dispute_pub.disp_header_rec;
   disp_line_rec           iex_dispute_pub.dispute_line_tbl;
 
   CURSOR c_user_id
   IS
      SELECT user_id
        FROM fnd_user
       WHERE user_name = UPPER ('STHALLAM');
 
   CURSOR c_org_id
   IS
      SELECT org_id
        FROM ra_customer_trx_all
       WHERE customer_trx_id = l_customer_trx_id;
BEGIN
   OPEN c_user_id;
 
   FETCH c_user_id
    INTO l_user_id;
 
   CLOSE c_user_id;
 
   OPEN c_org_id;
 
   FETCH c_org_id
    INTO l_org_id;
 
   CLOSE c_org_id;
 
   IF l_user_id IS NULL
   THEN
      DBMS_OUTPUT.put_line (' Please enter correct User Name ');
   END IF;
 
   IF l_org_id IS NULL
   THEN
      DBMS_OUTPUT.put_line ('Please enter correct Customer Trx Id ');
   END IF;
 
   mo_global.set_policy_context ('S', l_org_id);
   fnd_global.apps_initialize (l_user_id, l_responsibility_id, 695);
   arp_standard.enable_debug;
   disp_header_rec.cust_trx_id := l_customer_trx_id;
   disp_header_rec.line_amt := 1.00;
   disp_header_rec.tax_amt := 0.40;
   disp_header_rec.freight_amt := 0;
   disp_header_rec.cm_reason_code := 'A/R ERROR';
   disp_header_rec.line_credit_flag := 'N';
   disp_header_rec.delinquency_id := lv_delinquency_id_num;
   disp_header_rec.dispute_section := 'SPECIFIC_INV_LINES_TAX';
   disp_header_rec.attribute1 := 'XYZ';
 
   IF l_dispute_lines
   THEN
      disp_header_rec.line_credit_flag := 'Y';
      disp_line_rec (1).customer_trx_line_id := l_line_id_1;
      disp_line_rec (1).extended_amount := 1.00;
      disp_line_rec (1).quantity_credited := 1;
      disp_line_rec (1).attribute1 := 'XYZ';
   END IF;
 
   iex_dispute_pub.create_dispute (p_api_version             => 1.0,
                                   p_init_msg_list           => fnd_api.g_true,
                                   p_commit                  => fnd_api.g_false,
                                   p_disp_header_rec         => disp_header_rec,
                                   p_disp_line_tbl           => disp_line_rec,
                                   x_request_id              => lv_cm_request_id,
                                   x_return_status           => lv_return_status_txt,
                                   x_msg_count               => lv_msg_count_num,
                                   x_msg_data                => lv_msg_count_txt,
                                   p_skip_workflow_flag      => 'N',
                                   p_batch_source_name       => 'Trade Management'
                                  );
 
   IF lv_return_status_txt = 'S'
   THEN
      DBMS_OUTPUT.put_line (' Dispute created successfully ');
      DBMS_OUTPUT.put_line (' Credit Memo Req id is ' || lv_cm_request_id);
   END IF;
 
   DBMS_OUTPUT.put_line ('x_return_status : ' || lv_return_status_txt);
 
   IF lv_return_status_txt != 'S'
   THEN
      DBMS_OUTPUT.put_line (   'Error: '
                            || lv_return_status_txt
                            || '; '
                            || lv_msg_count_txt
                           );
 
      FOR lv_index_num IN 1 .. lv_msg_count_num
      LOOP
         lv_err_msg_stack_txt :=
              fnd_msg_pub.get (p_msg_index      => lv_index_num,
                               p_encoded        => 'F');
         DBMS_OUTPUT.put_line (lv_err_msg_stack_txt);
      END LOOP;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Exception raised => ' || SQLERRM);
END;
/
 
COMMIT ;

Import Points to Note:

  1. Dispute information is stored in table IEX_DISPUTES
  2. One credit memo request record in stored in table RA_CM_REQUESTS_ALL
  3. Line level credit memo information is stored in table RA_CM_REQUEST_LINES_ALL

Parameter P_SKIP_WORKFLOW_FLAG of API:
Defaults to N. If this value is set to Y, the entire workflow(ARCMREQ) is skipped and Credit Memo is directly created with NO notification triggered for User Approval on Credit Memo.

Refer to Post Create Dispute for creating Dispute through API>


For Further Reading

  1. API to add a Claim Line to already existing Claim
  2. PL/SQL Script to Create an Executable from backend
  3. Oracle Script to Create User Login Account
  4. Usage of PL/SQL Collections
  5. API to Create Customer Contacts
Tags:
OracleAppsDNA.com