eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AR_BILLS_MAIN SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 84


	Column 2 :  update_allowed      Varchar2(1) field with the values Y or N signifying if the field should be accessible
					to the user.

	Parameters IN

	pbr_module	: 	Varchar2(30) The forms module which contains fields for control.


	Call this function in the WHEN-NEW-FORM-INSTANCE trigger of the form which uses this pl sql table

*/


-- ==========================================================================================================================



function get_all_items(pbr_module IN VARCHAR2) RETURN blockitemtabtyp  IS

i INTEGER;

Line: 198


/* We need to reset all of the items back to being nonupdateble */

	newstatus:=pbr_blockitem;

Line: 204


	  	newstatus(i).update_allowed:='N';

Line: 220


			IF  brstate(i).update_allowed IN ('N')
				 THEN NULL;

Line: 227


					IF newstatus(j).update_allowed in ('N' , 'Y')           THEN
						 newstatus(j).update_allowed:=brstate(i).update_allowed;

Line: 258


                        IF  brstate(i).update_allowed IN ('Y') THEN NULL;

Line: 267


                                                 newstatus(j).update_allowed:='N';

Line: 325


brstate(236).update_allowed:='M';

Line: 330


brstate(238).update_allowed:='M';

Line: 335


brstate(239).update_allowed:='N';

Line: 340


brstate(235).update_allowed:='N';

Line: 345


brstate(237).update_allowed:='N';

Line: 350


brstate(241).update_allowed:='N';

Line: 355


brstate(233).update_allowed:='N';

Line: 360


brstate(234).update_allowed:='N';

Line: 365


brstate(242).update_allowed:='N';

Line: 370


brstate(240).update_allowed:='N';

Line: 375


brstate(232).update_allowed:='N';

Line: 380


brstate(49).update_allowed:='N';

Line: 385


brstate(51).update_allowed:='N';

Line: 390


brstate(52).update_allowed:='N';

Line: 395


brstate(48).update_allowed:='N';

Line: 400


brstate(50).update_allowed:='N';

Line: 405


brstate(54).update_allowed:='N';

Line: 410


brstate(46).update_allowed:='N';

Line: 415


brstate(47).update_allowed:='N';

Line: 420


brstate(55).update_allowed:='N';

Line: 425


brstate(53).update_allowed:='N';

Line: 430


brstate(45).update_allowed:='N';

Line: 435


brstate(115).update_allowed:='Y';

Line: 440


brstate(117).update_allowed:='Y';

Line: 445


brstate(118).update_allowed:='N';

Line: 450


brstate(114).update_allowed:='N';

Line: 455


brstate(116).update_allowed:='N';

Line: 460


brstate(120).update_allowed:='N';

Line: 465


brstate(112).update_allowed:='N';

Line: 470


brstate(113).update_allowed:='N';

Line: 475


brstate(121).update_allowed:='N';

Line: 480


brstate(119).update_allowed:='N';

Line: 485


brstate(111).update_allowed:='N';

Line: 490


brstate(82).update_allowed:='Y';

Line: 495


brstate(84).update_allowed:='Y';

Line: 500


brstate(85).update_allowed:='Y';

Line: 505


brstate(81).update_allowed:='Y';

Line: 510


brstate(83).update_allowed:='Y';

Line: 515


brstate(87).update_allowed:='Y';

Line: 520


brstate(79).update_allowed:='Y';

Line: 525


brstate(80).update_allowed:='Y';

Line: 530


brstate(88).update_allowed:='Y';

Line: 535


brstate(86).update_allowed:='Y';

Line: 540


brstate(78).update_allowed:='Y';

Line: 545


brstate(181).update_allowed:='Y';

Line: 550


brstate(183).update_allowed:='Y';

Line: 555


brstate(184).update_allowed:='Y';

Line: 560


brstate(180).update_allowed:='Y';

Line: 565


brstate(182).update_allowed:='Y';

Line: 570


brstate(186).update_allowed:='Y';

Line: 575


brstate(178).update_allowed:='Y';

Line: 580


brstate(179).update_allowed:='Y';

Line: 585


brstate(187).update_allowed:='Y';

Line: 590


brstate(185).update_allowed:='Y';

Line: 595


brstate(177).update_allowed:='Y';

Line: 600


brstate(71).update_allowed:='Y';

Line: 605


brstate(73).update_allowed:='N';

Line: 610


brstate(74).update_allowed:='N';

Line: 615


brstate(70).update_allowed:='N';

Line: 620


brstate(72).update_allowed:='N';

Line: 625


brstate(76).update_allowed:='N';

Line: 630


brstate(68).update_allowed:='N';

Line: 635


brstate(69).update_allowed:='N';

Line: 640


brstate(77).update_allowed:='N';

Line: 645


brstate(75).update_allowed:='N';

Line: 650


brstate(67).update_allowed:='N';

Line: 655


brstate(192).update_allowed:='M';

Line: 660


brstate(194).update_allowed:='N';

Line: 665


brstate(195).update_allowed:='N';

Line: 670


brstate(191).update_allowed:='N';

Line: 675


brstate(193).update_allowed:='N';

Line: 680


brstate(197).update_allowed:='N';

Line: 685


brstate(189).update_allowed:='N';

Line: 690


brstate(190).update_allowed:='N';

Line: 695


brstate(198).update_allowed:='N';

Line: 700


brstate(196).update_allowed:='N';

Line: 705


brstate(188).update_allowed:='N';

Line: 710


brstate(203).update_allowed:='M';

Line: 715


brstate(205).update_allowed:='N';

Line: 720


brstate(206).update_allowed:='N';

Line: 725


brstate(202).update_allowed:='N';

Line: 730


brstate(204).update_allowed:='N';

Line: 735


brstate(208).update_allowed:='N';

Line: 740


brstate(200).update_allowed:='N';

Line: 745


brstate(201).update_allowed:='N';

Line: 750


brstate(209).update_allowed:='N';

Line: 755


brstate(207).update_allowed:='N';

Line: 760


brstate(199).update_allowed:='N';

Line: 765


brstate(38).update_allowed:='M';

Line: 770


brstate(40).update_allowed:='N';

Line: 775


brstate(41).update_allowed:='N';

Line: 780


brstate(37).update_allowed:='N';

Line: 785


brstate(39).update_allowed:='N';

Line: 790


brstate(43).update_allowed:='N';

Line: 795


brstate(35).update_allowed:='N';

Line: 800


brstate(36).update_allowed:='N';

Line: 805


brstate(44).update_allowed:='N';

Line: 810


brstate(42).update_allowed:='N';

Line: 815


brstate(34).update_allowed:='N';

Line: 820


brstate(93).update_allowed:='Y';

Line: 825


brstate(95).update_allowed:='N';

Line: 830


brstate(96).update_allowed:='N';

Line: 835


brstate(92).update_allowed:='N';

Line: 840


brstate(94).update_allowed:='N';

Line: 845


brstate(98).update_allowed:='N';

Line: 850


brstate(90).update_allowed:='N';

Line: 855


brstate(91).update_allowed:='N';

Line: 860


brstate(99).update_allowed:='N';

Line: 865


brstate(97).update_allowed:='N';

Line: 870


brstate(89).update_allowed:='N';

Line: 875


brstate(214).update_allowed:='Y';

Line: 880


brstate(216).update_allowed:='N';

Line: 885


brstate(217).update_allowed:='N';

Line: 890


brstate(213).update_allowed:='N';

Line: 895


brstate(215).update_allowed:='N';

Line: 900


brstate(219).update_allowed:='N';

Line: 905


brstate(211).update_allowed:='N';

Line: 910


brstate(212).update_allowed:='N';

Line: 915


brstate(220).update_allowed:='N';

Line: 920


brstate(218).update_allowed:='N';

Line: 925


brstate(210).update_allowed:='N';

Line: 930


brstate(225).update_allowed:='M';

Line: 935


brstate(227).update_allowed:='M';

Line: 940


brstate(228).update_allowed:='N';

Line: 945


brstate(224).update_allowed:='N';

Line: 950


brstate(226).update_allowed:='N';

Line: 955


brstate(230).update_allowed:='N';

Line: 960


brstate(222).update_allowed:='N';

Line: 965


brstate(223).update_allowed:='N';

Line: 970


brstate(231).update_allowed:='N';

Line: 975


brstate(229).update_allowed:='N';

Line: 980


brstate(221).update_allowed:='N';

Line: 985


brstate(170).update_allowed:='Y';

Line: 990


brstate(172).update_allowed:='Y';

Line: 995


brstate(173).update_allowed:='Y';

Line: 1000


brstate(169).update_allowed:='N';

Line: 1005


brstate(171).update_allowed:='N';

Line: 1010


brstate(175).update_allowed:='N';

Line: 1015


brstate(167).update_allowed:='N';

Line: 1020


brstate(168).update_allowed:='N';

Line: 1025


brstate(176).update_allowed:='Y';

Line: 1030


brstate(174).update_allowed:='Y';

Line: 1035


brstate(166).update_allowed:='N';

Line: 1040


brstate(159).update_allowed:='Y';

Line: 1045


brstate(161).update_allowed:='Y';

Line: 1050


brstate(162).update_allowed:='Y';

Line: 1055


brstate(158).update_allowed:='Y';

Line: 1060


brstate(160).update_allowed:='Y';

Line: 1065


brstate(164).update_allowed:='Y';

Line: 1070


brstate(156).update_allowed:='Y';

Line: 1075


brstate(157).update_allowed:='Y';

Line: 1080


brstate(165).update_allowed:='Y';

Line: 1085


brstate(163).update_allowed:='Y';

Line: 1090


brstate(155).update_allowed:='Y';

Line: 1095


brstate(27).update_allowed:='Y';

Line: 1100


brstate(29).update_allowed:='Y';

Line: 1105


brstate(30).update_allowed:='Y';

Line: 1110


brstate(26).update_allowed:='N';

Line: 1115


brstate(28).update_allowed:='N';

Line: 1120


brstate(32).update_allowed:='N';

Line: 1125


brstate(24).update_allowed:='N';

Line: 1130


brstate(25).update_allowed:='N';

Line: 1135


brstate(33).update_allowed:='Y';

Line: 1140


brstate(31).update_allowed:='N';

Line: 1145


brstate(23).update_allowed:='N';

Line: 1150


brstate(16).update_allowed:='Y';

Line: 1155


brstate(18).update_allowed:='Y';

Line: 1160


brstate(19).update_allowed:='Y';

Line: 1165


brstate(15).update_allowed:='N';

Line: 1170


brstate(17).update_allowed:='N';

Line: 1175


brstate(21).update_allowed:='N';

Line: 1180


brstate(13).update_allowed:='N';

Line: 1185


brstate(14).update_allowed:='N';

Line: 1190


brstate(22).update_allowed:='Y';

Line: 1195


brstate(20).update_allowed:='N';

Line: 1200


brstate(12).update_allowed:='N';

Line: 1205


brstate(247).update_allowed:='Y';

Line: 1210


brstate(249).update_allowed:='Y';

Line: 1215


brstate(250).update_allowed:='Y';

Line: 1220


brstate(246).update_allowed:='N';

Line: 1225


brstate(248).update_allowed:='N';

Line: 1230


brstate(252).update_allowed:='N';

Line: 1235


brstate(244).update_allowed:='N';

Line: 1240


brstate(245).update_allowed:='N';

Line: 1245


brstate(253).update_allowed:='Y';

Line: 1250


brstate(251).update_allowed:='N';

Line: 1255


brstate(243).update_allowed:='N';

Line: 1260


brstate(5).update_allowed:='Y';

Line: 1265


brstate(7).update_allowed:='Y';

Line: 1270


brstate(8).update_allowed:='Y';

Line: 1275


brstate(4).update_allowed:='N';

Line: 1280


brstate(6).update_allowed:='N';

Line: 1285


brstate(10).update_allowed:='N';

Line: 1290


brstate(2).update_allowed:='N';

Line: 1295


brstate(3).update_allowed:='N';

Line: 1300


brstate(11).update_allowed:='Y';

Line: 1305


brstate(9).update_allowed:='N';

Line: 1310


brstate(1).update_allowed:='N';

Line: 1315


brstate(148).update_allowed:='Y';

Line: 1320


brstate(150).update_allowed:='Y';

Line: 1325


brstate(151).update_allowed:='Y';

Line: 1330


brstate(147).update_allowed:='Y';

Line: 1335


brstate(149).update_allowed:='Y';

Line: 1340


brstate(153).update_allowed:='N';

Line: 1345


brstate(145).update_allowed:='Y';

Line: 1350


brstate(146).update_allowed:='Y';

Line: 1355


brstate(154).update_allowed:='Y';

Line: 1360


brstate(152).update_allowed:='Y';

Line: 1365


brstate(144).update_allowed:='Y';

Line: 1370


brstate(126).update_allowed:='Y';

Line: 1375


brstate(128).update_allowed:='Y';

Line: 1380


brstate(129).update_allowed:='Y';

Line: 1385


brstate(125).update_allowed:='Y';

Line: 1390


brstate(127).update_allowed:='N';

Line: 1395


brstate(131).update_allowed:='N';

Line: 1400


brstate(123).update_allowed:='N';

Line: 1405


brstate(124).update_allowed:='N';

Line: 1410


brstate(132).update_allowed:='Y';

Line: 1415


brstate(130).update_allowed:='N';

Line: 1420


brstate(122).update_allowed:='N';

Line: 1425


brstate(60).update_allowed:='M';

Line: 1430


brstate(62).update_allowed:='N';

Line: 1435


brstate(63).update_allowed:='N';

Line: 1440


brstate(59).update_allowed:='N';

Line: 1445


brstate(61).update_allowed:='N';

Line: 1450


brstate(65).update_allowed:='N';

Line: 1455


brstate(57).update_allowed:='N';

Line: 1460


brstate(58).update_allowed:='N';

Line: 1465


brstate(66).update_allowed:='N';

Line: 1470


brstate(64).update_allowed:='N';

Line: 1475


brstate(56).update_allowed:='N';

Line: 1480


brstate(104).update_allowed:='M';

Line: 1485


brstate(106).update_allowed:='N';

Line: 1490


brstate(107).update_allowed:='N';

Line: 1495


brstate(103).update_allowed:='N';

Line: 1500


brstate(105).update_allowed:='N';

Line: 1505


brstate(109).update_allowed:='N';

Line: 1510


brstate(101).update_allowed:='N';

Line: 1515


brstate(102).update_allowed:='N';

Line: 1520


brstate(110).update_allowed:='N';

Line: 1525


brstate(108).update_allowed:='N';

Line: 1530


brstate(100).update_allowed:='N';

Line: 1535


brstate(137).update_allowed:='M';

Line: 1540


brstate(139).update_allowed:='N';

Line: 1545


brstate(140).update_allowed:='N';

Line: 1550


brstate(136).update_allowed:='N';

Line: 1555


brstate(138).update_allowed:='N';

Line: 1560


brstate(142).update_allowed:='N';

Line: 1565


brstate(134).update_allowed:='N';

Line: 1570


brstate(135).update_allowed:='N';

Line: 1575


brstate(143).update_allowed:='N';

Line: 1580


brstate(141).update_allowed:='N';

Line: 1585


brstate(133).update_allowed:='N';

Line: 1591


brstate( 254).update_allowed:='Y';

Line: 1596


brstate( 255).update_allowed:='Y';

Line: 1601


brstate( 256).update_allowed:='Y';

Line: 1606


brstate( 257).update_allowed:='M';

Line: 1611


brstate( 258).update_allowed:='M';

Line: 1616


brstate( 259).update_allowed:='M';

Line: 1621


brstate( 260).update_allowed:='Y';

Line: 1626


brstate( 261).update_allowed:='Y';

Line: 1631


brstate( 262).update_allowed:='M';

Line: 1636


brstate( 263).update_allowed:='M';

Line: 1641


brstate( 264).update_allowed:='Y';

Line: 1646


brstate( 265).update_allowed:='Y';

Line: 1651


brstate( 266).update_allowed:='M';

Line: 1656


brstate( 267).update_allowed:='Y';

Line: 1661


brstate( 268).update_allowed:='Y';

Line: 1666


brstate( 269).update_allowed:='Y';

Line: 1671


brstate( 270).update_allowed:='Y';

Line: 1676


brstate( 271).update_allowed:='M';

Line: 1681


brstate( 272).update_allowed:='M';

Line: 1686


brstate( 273).update_allowed:='Y';

Line: 1691


brstate( 274).update_allowed:='M';

Line: 1696


brstate( 275).update_allowed:='M';

Line: 1701


brstate( 276).update_allowed:='Y';

Line: 1710


brstate( 277).update_allowed:='N';

Line: 1715


brstate( 278).update_allowed:='N';

Line: 1720


brstate( 279).update_allowed:='Y';

Line: 1725


brstate( 280).update_allowed:='N';

Line: 1730


brstate( 281).update_allowed:='N';

Line: 1735


brstate( 282).update_allowed:='N';

Line: 1740


brstate( 283).update_allowed:='Y';

Line: 1745


brstate( 284).update_allowed:='N';

Line: 1750


brstate( 285).update_allowed:='Y';

Line: 1755


brstate( 286).update_allowed:='N';

Line: 1760


brstate( 287).update_allowed:='Y';

Line: 1765


brstate( 288).update_allowed:='Y';

Line: 1770


brstate( 289).update_allowed:='N';

Line: 1775


brstate( 290).update_allowed:='N';

Line: 1780


brstate( 291).update_allowed:='N';

Line: 1785


brstate( 292).update_allowed:='N';

Line: 1790


brstate( 293).update_allowed:='N';

Line: 1795


brstate( 294).update_allowed:='Y';

Line: 1800


brstate( 295).update_allowed:='Y';

Line: 1805


brstate( 296).update_allowed:='Y';

Line: 1810


brstate( 297).update_allowed:='Y';

Line: 1815


brstate( 298).update_allowed:='N';

Line: 1820


brstate( 299).update_allowed:='N';

Line: 1826


brstate( 300).update_allowed:='Y';

Line: 1831


brstate( 301).update_allowed:='Y';

Line: 1836


brstate( 302).update_allowed:='Y';

Line: 1841


brstate( 303).update_allowed:='Y';

Line: 1846


brstate( 304).update_allowed:='N';

Line: 1851


brstate( 305).update_allowed:='N';

Line: 1856


brstate( 306).update_allowed:='N';

Line: 1861


brstate( 307).update_allowed:='N';

Line: 1866


brstate( 308).update_allowed:='Y';

Line: 1871


brstate( 309).update_allowed:='N';

Line: 1876


brstate( 310).update_allowed:='N';

Line: 1881


brstate( 311).update_allowed:='N';

Line: 1886


brstate( 312).update_allowed:='Y';

Line: 1891


brstate( 313).update_allowed:='N';

Line: 1896


brstate( 314).update_allowed:='Y';

Line: 1901


brstate( 315).update_allowed:='N';

Line: 1906


brstate( 316).update_allowed:='Y';

Line: 1911


brstate( 317).update_allowed:='Y';

Line: 1916


brstate( 318).update_allowed:='N';

Line: 1921


brstate( 319).update_allowed:='N';

Line: 1926


brstate( 320).update_allowed:='N';

Line: 1931


brstate( 321).update_allowed:='N';

Line: 1936


brstate( 322).update_allowed:='N';

Line: 1940


brstate( 323).br_state:='SELECTED';

Line: 1941


brstate( 323).update_allowed:='N';

Line: 1945


brstate( 324).br_state:='SELECTED';

Line: 1946


brstate( 324).update_allowed:='N';

Line: 1950


brstate( 325).br_state:='SELECTED';

Line: 1951


brstate( 325).update_allowed:='N';

Line: 1955


brstate( 326).br_state:='SELECTED';

Line: 1956


brstate( 326).update_allowed:='N';

Line: 1960


brstate( 327).br_state:='SELECTED';

Line: 1961


brstate( 327).update_allowed:='N';

Line: 1965


brstate( 328).br_state:='SELECTED';

Line: 1966


brstate( 328).update_allowed:='N';

Line: 1970


brstate( 329).br_state:='SELECTED';

Line: 1971


brstate( 329).update_allowed:='N';

Line: 1975


brstate( 330).br_state:='SELECTED';

Line: 1976


brstate( 330).update_allowed:='N';

Line: 1980


brstate( 331).br_state:='SELECTED';

Line: 1981


brstate( 331).update_allowed:='Y';

Line: 1985


brstate( 332).br_state:='SELECTED';

Line: 1986


brstate( 332).update_allowed:='N';

Line: 1990


brstate( 333).br_state:='SELECTED';

Line: 1991


brstate( 333).update_allowed:='N';

Line: 1995


brstate( 334).br_state:='SELECTED';

Line: 1996


brstate( 334).update_allowed:='N';

Line: 2000


brstate( 335).br_state:='SELECTED';

Line: 2001


brstate( 335).update_allowed:='N';

Line: 2005


brstate( 336).br_state:='SELECTED';

Line: 2006


brstate( 336).update_allowed:='N';

Line: 2010


brstate( 337).br_state:='SELECTED';

Line: 2011


brstate( 337).update_allowed:='Y';

Line: 2015


brstate( 338).br_state:='SELECTED';

Line: 2016


brstate( 338).update_allowed:='N';

Line: 2020


brstate( 339).br_state:='SELECTED';

Line: 2021


brstate( 339).update_allowed:='N';

Line: 2025


brstate( 340).br_state:='SELECTED';

Line: 2026


brstate( 340).update_allowed:='Y';

Line: 2030


brstate( 341).br_state:='SELECTED';

Line: 2031


brstate( 341).update_allowed:='N';

Line: 2035


brstate( 342).br_state:='SELECTED';

Line: 2036


brstate( 342).update_allowed:='N';

Line: 2040


brstate( 343).br_state:='SELECTED';

Line: 2041


brstate( 343).update_allowed:='N';

Line: 2045


brstate( 344).br_state:='SELECTED';

Line: 2046


brstate( 344).update_allowed:='N';

Line: 2050


brstate( 345).br_state:='SELECTED';

Line: 2051


brstate( 345).update_allowed:='N';

Line: 2139


Cursor Cposted (CustomerTrxId NUMBER) IS SELECT
customer_trx_id
from ar_transaction_history
where customer_trx_id = CUSTOMERTRXID
and posting_control_id <> -3
and gl_posted_date IS NOT NULL;

Line: 2162


FUNCTION br_selected(p_customer_trx_id in number) return VARCHAR2 IS

cursor Cselremit(CustomerTrxId NUMBER)
IS SELECT
customer_trx_id
FROM
ar_batches b ,
ar_payment_schedules p
where p.customer_trx_id = CUSTOMERTRXID
and p.reserved_type = 'REMITTANCE'
and p.reserved_value=b.batch_id
and b.status='OP';

Line: 2186


		RETURN 'SELECTED';

Line: 2194


END br_selected;

Line: 2234


   /* modified for tca update */
   /* bug 1637367 Removed ; introduced in 115.11 (modifications for TCA update) */

Line: 2239


'Select
 TRX.trx_number,
 TRX.doc_sequence_value,
 TRX.trx_date,
 TRX.comments,
 TRX.purchase_order,
 TRX.invoice_currency_code,
 LINES.customer_trx_id,
 NULL,
 PAYS.amount_due_original,
 PAYS.amount_due_remaining,
 PAYS.acctd_amount_due_remaining,
 PAYS.due_date,
 PAYS.exchange_rate,
 PAYS.terms_sequence_number,
 PARTY.jgzz_fiscal_code,
 CUST_ACCT.account_number,
 substrb(party.party_name,1,50),
 CUST_ACCT.customer_class_code,
 PARTY.category_code,
 ARPT_SQL_FUNC_UTIL.get_lookup_meaning(''CUSTOMER_CATEGORY'', party.category_code),
 ARPT_SQL_FUNC_UTIL.get_trx_type_details(TRX.cust_trx_type_id,''NAME''),
 ARPT_SQL_FUNC_UTIL.get_lookup_meaning(''INV/CM'', types.type),
 ARPT_SQL_FUNC_UTIL.get_trx_type_details(TRX.cust_trx_type_id,''TYPE''),
 METH.name,
 METH.receipt_method_id,
 SITE.location,
 TRX.bill_to_site_use_id,
 ABB.bank_name,
 ABB.branch_party_id bank_branch_id,
 ABA.bank_account_id,
 CONS.cons_billing_number,
 CONS.cons_inv_id,
 LINES.br_ref_customer_trx_id,
 LINES.br_ref_payment_schedule_id,
 LINES.extended_amount,
 LINES.extended_acctd_amount,
 LINES.customer_trx_line_id
 FROM
 RA_CUSTOMER_TRX         TRX ,
 RA_CUST_TRX_TYPES 	 TYPES,
 AR_PAYMENT_SCHEDULES    PAYS ,
 HZ_CUST_ACCOUNTS        CUST_ACCT ,
 HZ_PARTIES		 PARTY,
 AR_RECEIPT_METHODS      METH,
 CE_BANK_BRANCHES_V      ABB,
 HZ_CUST_SITE_USES       SITE,
 AP_BANK_ACCOUNTS        ABA,
 RA_CUSTOMER_TRX_LINES 	 LINES ,
 AR_CONS_INV_ALL	 CONS
 WHERE trx.legal_entity_id = ' || p_le_id ||
 ' and trx.bill_to_customer_id = cust_acct.cust_account_id
 and trx.cust_trx_type_id = types.cust_trx_type_id
 and cust_acct.party_id = party.party_id
 and trx.customer_trx_id = pays.customer_trx_id
 and pays.payment_schedule_id = lines.br_ref_payment_schedule_id
 and site.site_use_id = trx.bill_to_site_use_id
 and trx.customer_bank_account_id = aba.bank_account_id (+)
 and aba.bank_branch_id = abb.branch_party_id (+)
 and cons.cons_inv_id(+) = pays.cons_inv_id
 and trx.receipt_method_id = meth.receipt_method_id (+)
 and lines.customer_trx_id = '|| Customer_trx_id;

Line: 2305


      arp_util.debug(  'Inserting the existing assignments into the table ');

Line: 2342


   select '''' || invoice_currency_code ||'''',
          'to_dat'||'e(''' ||trx_date||''')'
     into br_currency,
          br_trxdate
     from ra_customer_trx
    where customer_trx_id = br_trxid;

Line: 2352


      arp_util.debug(  'Inserting the new assignments ');

Line: 2356


   /* modified for tca update */
   /* bug7046838 : Modified the query for performance improvement. */
AssignCurStr:=
'Select
 TRX.trx_number,
 TRX.doc_sequence_value,
 TRX.trx_date,
 TRX.comments,
 TRX.purchase_order,
 TRX.invoice_currency_code,
 NULL,
 NULL,
 PAYS.amount_due_original,
 -- bug 2473700 NULL,
 PAYS.amount_due_remaining,
 NULL,
 PAYS.due_date,
 PAYS.exchange_rate,
 PAYS.terms_sequence_number,
 PARTY.jgzz_fiscal_code,
 CUST_ACCT.account_number,
 substrb(PARTY.PARTY_name,1,50),
 CUST_ACCT.customer_class_code,
 PARTY.category_code,
 ARPT_SQL_FUNC_UTIL.get_lookup_meaning(''CUSTOMER_CATEGORY'', party.category_code),
 ARPT_SQL_FUNC_UTIL.get_trx_type_details(TRX.cust_trx_type_id,''NAME''),
 ARPT_SQL_FUNC_UTIL.get_lookup_meaning(''INV/CM'', types.type),
 ARPT_SQL_FUNC_UTIL.get_trx_type_details(TRX.cust_trx_type_id,''TYPE''),
 METH.name,
 METH.receipt_method_id,
 SITE.location,
 TRX.bill_to_site_use_id,
 ABB.bank_name,
 ABB.branch_party_id bank_branch_id,
 ABA.bank_account_id,
 NULL,
 NULL,
 TRX.customer_trx_id,             /* br_ref_customer_trx_id */
 PAYS.payment_schedule_id,        /* br_ref_payment_schedule_id */
 PAYS.amount_due_remaining,       /* Extended Amount */
 PAYS.acctd_amount_due_remaining, /* Extended Acctd Amount */
 NULL				  /* Customer_trx_line_id */
 FROM
 RA_CUST_TRX_TYPES	 TYPES,
 RA_CUSTOMER_TRX         TRX ,
 AR_PAYMENT_SCHEDULES    PAYS ,
 HZ_CUST_ACCOUNTS        CUST_ACCT ,
 HZ_PARTIES		 PARTY,
 AR_receipt_METHODS      METH,
 CE_BANK_BRANCHES_V      ABB,
 HZ_CUST_SITE_USES       SITE,
 ap_bank_accounts        ABA
 where trx.legal_entity_id = ' || p_le_id ||
' and decode(pays.class,''BR'', TRX.drawee_id, TRX.bill_to_customer_id) = cust_acct.cust_account_id
 and CUST_ACCT.party_id = PARTY.party_id
 and TRX.customer_trx_id = PAYS.customer_trx_id
 and TRX.cust_trx_type_id = TYPES.cust_trx_type_id
 and PAYS.reserved_type IS NULL
 and PAYS.amount_due_remaining <> 0
 and SITE.SITE_USE_ID = TRX.BILL_TO_SITE_USE_ID
 and TRX.CUSTOMER_BANK_ACCOUNT_ID = ABA.BANK_ACCOUNT_ID (+)
 and ABA.BANK_BRANCH_ID = ABB.branch_party_id (+)
 and trx.receipt_method_id = meth.receipt_method_id (+)
 and PAYS.selected_for_receipt_batch_id is null
 and not exists
 (select 1
    from ra_customer_trx_lines sub,
         ar_payment_schedules ps
   where sub.br_ref_customer_trx_id = TRX.customer_trx_id
     and ps.customer_trx_id = trx.customer_trx_id
     and ( ( ps.amount_due_remaining = 0 and
             sub.customer_trx_id <> ' || customer_trx_id || ' )' ||
           ' OR (sub.customer_trx_id = ' || customer_trx_id || ' )))' ||
' and trx.invoice_currency_code = ' || br_currency ||
' and trx.trx_date <= ' || br_trxdate;

Line: 2432


   /* modified for tca update */
   if PAY_UNRELATED_INVOICES = 'N' THEN
      AssignCurStr := AssignCurStr ||
        ' and   trx.bill_to_customer_id in (
		SELECT rr.related_cust_account_id
		FROM hz_cust_acct_relate rr
		WHERE rr.cust_account_id = ' || drawee_id ||
	' and rr.related_cust_account_id = trx.bill_to_customer_id
          and rr.bill_to_flag = ''Y'' ' ||
		' UNION ' ||
        ' SELECT ' || drawee_id  || ' from dual ' ||
		' UNION ' ||
		' SELECT rel.related_cust_account_id
            FROM ar_paying_relationships_v rel,
                 hz_cust_accounts acc
           WHERE rel.party_id = acc.party_id
	     AND rel.related_cust_account_id = trx.bill_to_customer_id
             AND acc.cust_account_id = ' || drawee_id ||
            ' AND ' || br_trxdate || ' BETWEEN effective_start_date
                                    AND effective_end_date) ';

Line: 2459


   /* modified for tca update */
AssignCurStr:=AssignCurStr||
'UNION Select
 TRX.trx_number,
 TRX.doc_sequence_value,
 TRX.trx_date,
 TRX.comments,
 TRX.purchase_order,
 TRX.invoice_currency_code,
 NULL,
 NULL,
 PAYS.amount_due_original,
 -- Bug 2473700 NULL,
 PAYS.amount_due_remaining,
 NULL,
 PAYS.due_date,
 PAYS.exchange_rate,
 PAYS.terms_sequence_number,
 PARTY.jgzz_fiscal_code,
 CUST_ACCT.account_number,
 substrb(party.party_name,1,50),
 CUST_ACCT.customer_class_code,
 PARTY.category_code,
 ARPT_SQL_FUNC_UTIL.get_lookup_meaning(''CUSTOMER_CATEGORY'', party.category_code),
 ARPT_SQL_FUNC_UTIL.get_trx_type_details(TRX.cust_trx_type_id,''NAME''),
 ARPT_SQL_FUNC_UTIL.get_lookup_meaning(''INV/CM'', types.type),
 ARPT_SQL_FUNC_UTIL.get_trx_type_details(TRX.cust_trx_type_id,''TYPE''),
 METH.name,
 METH.receipt_method_id,
 SITE.location,
 TRX.bill_to_site_use_id,
 ABB.bank_name,
 ABB.branch_party_id bank_branch_id,
 ABA.bank_account_id,
 NULL,
 NULL,
 TRX.customer_trx_id,  			/* br_ref_customer_trx_id */
 PAYS.payment_schedule_id,  		/* br_ref_payment_schedule_id */
 PAYS.amount_due_remaining,  		/* Extended Amount */
 PAYS.acctd_amount_due_remaining,  	/* Extended Acctd Amount */
 NULL					/* Customer_TRX_LINE_ID */
 FROM
 RA_CUST_TRX_TYPES	 TYPES,
 RA_CUSTOMER_TRX         TRX ,
 AR_PAYMENT_SCHEDULES    PAYS ,
 HZ_CUST_ACCOUNTS        CUST_ACCT ,
 HZ_PARTIES		 PARTY,
 AR_receipt_METHODS      METH,
 CE_BANK_BRANCHES_V      ABB,
 HZ_CUST_SITE_USES       SITE,
 ap_bank_accounts        ABA,
 AR_TRANSACTION_HISTORY  H
 where trx.legal_entity_id = ' || p_le_id ||
' and TRX.drawee_id = cust_acct.cust_account_id
 and cust_acct.party_id = party.party_id
 and TRX.customer_trx_id = PAYS.customer_trx_id
 and TRX.cust_trx_type_id = TYPES.cust_trx_type_id
 and PAYS.amount_due_remaining <> 0
 and PAYS.amount_due_remaining = TRX.br_amount
 and PAYS.selected_for_receipt_batch_id is null
 and SITE.SITE_USE_ID =  TRX.drawee_site_use_id
 and TRX.CUSTOMER_BANK_ACCOUNT_ID = ABA.BANK_ACCOUNT_ID (+)
 and ABA.BANK_BRANCH_ID = ABB.branch_party_id (+)
 and trx.receipt_method_id = meth.receipt_method_id (+)
 and H.customer_trx_id = TRX.customer_trx_id
 and H.current_record_flag    = ''Y''
 and H.status = ''UNPAID''
 and not exists
(select 1
   from ra_customer_trx_lines 	linesub,
        ar_payment_schedules 	paysub ,
        ra_customer_trx 	billsub
  where linesub.br_ref_customer_trx_id = TRX.customer_trx_id
    and linesub.customer_trx_id = billsub.customer_trx_id
    and billsub.customer_trx_id  = paysub.customer_trx_id
    and (paysub.reserved_type is not null OR billsub.br_on_hold_flag = ''Y''))
 and trx.invoice_currency_code = ' || br_currency ||
' and trx.trx_date <= ' || br_trxdate;

Line: 2538


   /* modified for tca update */
   if PAY_UNRELATED_INVOICES='N' THEN
      AssignCurStr := AssignCurStr||
         ' and   TRX.drawee_id in (
		SELECT rr.related_cust_account_id
		FROM hz_cust_acct_relate rr
		WHERE rr.cust_account_id = ' || drawee_id ||
	' and rr.related_cust_account_id = trx.bill_to_customer_id
          and rr.bill_to_flag = ''Y'' ' ||
		' UNION ' ||
        ' SELECT ' || drawee_id  || ' from dual ' ||
		' UNION ' ||
		' SELECT rel.related_cust_account_id
            FROM ar_paying_relationships_v rel,
                 hz_cust_accounts acc
           WHERE rel.party_id = acc.party_id
	     AND rel.related_cust_account_id = trx.bill_to_customer_id
             AND acc.cust_account_id = ' || drawee_id ||
            ' AND ' || br_trxdate || ' BETWEEN effective_start_date
                                    AND effective_end_date) ';